query in drop_partitions

maks

Hello. I have a table divided into partitions by date. I want to delete the oldest partition (I calculate the value of this partition so:
SELECT MAX (CAST(time AS DATE)) FROM mytable;

Now I want to delete this partition, I try like this:
SELECT DROP_PARTITIONS ('mytable','SELECT MAX (CAST(time AS DATE)) FROM mytable','SELECT MAX (CAST(time AS DATE)) FROM mytable');

But I get an error:
Failed to convert to internal form: Invalid input syntax for date
ERROR 3721: Invalid partition key

How can I use query in DROP_PARTITIONS?

marcothesane

With this exact script:

-- find the oldest partition key in the table, and embed that into a working SQL statement
\t
-- tuples only in output
\a
-- do not align the output
SELECT
  'SELECT DROP_PARTITIONS('||CHR(10)||
  '  ''flex.drp_p'''||CHR(10)||
  ', '||QUOTE_LITERAL(MIN(prk)::VARCHAR(10))||CHR(10)||
  ', '||QUOTE_LITERAL(MIN(prk)::VARCHAR(10))||CHR(10)||
  ');'
FROM flex.drp_p;

I can do it without creating an intermediate file, by piping the first output of vsql into a second vsql session:

$ vsql -qf d.sql | vsql
SET
  DROP_PARTITIONS  
-------------------
 Partition dropped
(1 row)

Sankarmn

@maks, are you selecting the partition key column for the drop? (from the error)

What output you get for your select SQL?

Bryan_H

Try this:
SELECT MAX(DATE(time)) FROM mytable;
This will simplify the CAST/CONVERT requirement. Partition keys are stored as VARCHAR so another cast step may be needed.

maks

What output you get for your select SQL?

I get date value, for example, 2020-05-23

maks

Try this:
SELECT MAX(DATE(time)) FROM mytable;

I try but I get the same error Failed to convert to internal form: Invalid input syntax for date

marcothesane

Ensure that you are using the right partition key format:
Try:

SELECT DISTINCT
  partition_key
FROM partitions
JOIN projections USING(projection_id)
WHERE table_schema='my_schema'  AND anchor_table_name= 'mytable'

 partition_key 
---------------
 2019-01-01
 2019-01-02
 2019-01-03
 2019-01-04
 2019-01-05
 2019-01-06
 2019-01-07
 2019-01-08
 2019-01-09
 2019-01-10

You'll see the format of the keys.
But:

SELECT DROP_PARTITIONS('flex.drp_p'                                                 
                      ,(SELECT MIN(prk)::VARCHAR(10) FROM flex.drp_p)
                      ,(SELECT MIN(prk)::VARCHAR(10) FROM flex.drp_p)
                      );
ERROR 5301:  Unsupported use of sub-queries.

So, SQL generating SQL:

\t
\a
SELECT
  'SELECT DROP_PARTITIONS('||CHR(10)||
  '  ''flex.drp_p'''||CHR(10)||
  ', '||QUOTE_LITERAL(MIN(prk)::VARCHAR(10))||CHR(10)||
  ', '||QUOTE_LITERAL(MIN(prk)::VARCHAR(10))||CHR(10)||
  ');'
FROM flex.drp_p;

Result:

SELECT DROP_PARTITIONS(
  'flex.drp_p'
, '2019-01-01'
, '2019-01-01'
);

marcothesane

Once generated that statement (to file), you just run that file.

maks

marcothesane, I'm very sorry but I don't understand how to do from your query real query that delete partitions

marcothesane

Here's my full example:
The table:

DROP TABLE IF EXISTS drp_p;
CREATE TABLE IF NOT EXISTS drp_p (
  prk DATE NOT NULL
, des VARCHAR(32)
, pr  NUMERIC(5,2)
) 
PARTITION BY prk
;
INSERT INTO drp_p
          SELECT DATE '2019-01-01','nm_10', 1.25 
UNION ALL SELECT DATE '2019-01-01','2ndnm_01', 1.75
UNION ALL SELECT DATE '2019-01-02','nm_11', 1.25 
UNION ALL SELECT DATE '2019-01-02','2ndnm_02', 1.75
UNION ALL SELECT DATE '2019-01-03','nm_12', 1.25
UNION ALL SELECT DATE '2019-01-03','2ndnm_03', 1.75
UNION ALL SELECT DATE '2019-01-04','nm_13', 1.25
UNION ALL SELECT DATE '2019-01-04','2ndnm_04', 1.75
UNION ALL SELECT DATE '2019-01-05','nm_14', 1.25
UNION ALL SELECT DATE '2019-01-05','2ndnm_05', 1.75
UNION ALL SELECT DATE '2019-01-06','nm_15', 1.25
UNION ALL SELECT DATE '2019-01-06','2ndnm_06', 1.75
UNION ALL SELECT DATE '2019-01-07','nm_16', 1.25
UNION ALL SELECT DATE '2019-01-07','2ndnm_07', 1.75
UNION ALL SELECT DATE '2019-01-08','nm_17', 1.25
UNION ALL SELECT DATE '2019-01-08','2ndnm_08', 1.75
UNION ALL SELECT DATE '2019-01-09','nm_18', 1.25
UNION ALL SELECT DATE '2019-01-09','2ndnm_09', 1.75
UNION ALL SELECT DATE '2019-01-10','nm_19', 1.25
UNION ALL SELECT DATE '2019-01-10','2ndnm_10', 1.75
;
COMMIT;

Here is a query to find out all partition keys of the table drp_p :

SELECT DISTINCT
  partition_key
FROM partitions
JOIN projections USING(projection_id)
WHERE  anchor_table_name= 'drp_p'
ORDER BY partition_key
;
partition_key 
--------------
2019-01-01
2019-01-02
2019-01-03
2019-01-04
2019-01-05
2019-01-06
2019-01-07
2019-01-08
2019-01-09
2019-01-10

The table is partitioned by prk, which is a DATE. The oldest partition key is 2019-01-01. This would need to be dropped.

You cannot use subselects in a call to DROP_PARTITIONS() :, as I said in my previous post.

The solution is to generate a new SQL script, while querying for the oldest partition key.

-- find the oldest partition key in the table, and embed that into a working SQL statement
\t
-- tuples only in output
\a
-- do not align the output
SELECT
  'SELECT DROP_PARTITIONS('||CHR(10)||
  '  ''flex.drp_p'''||CHR(10)||
  ', '||Q``UOTE_LITERAL(MIN(prk)::VARCHAR(10))||CHR(10)||
  ', '||QUOTE_LITERAL(MIN(prk)::VARCHAR(10))||CHR(10)||
  ');'
FROM flex.drp_p;

If the script above is in a file called generate.sql, you can generate it like so:
$ vsql -Xqf generate.sql > doit.sql
Find the output of the query above here below.

SELECT DROP_PARTITIONS(
  'flex.drp_p'
, '2019-01-01'
, '2019-01-01'
);

Then, go: $ vsql -f doit.sql , to actually drop the partition.

maks

marcothesane, thanks a lot. In file doit.sql I have additional strings: first string ?column? and last string (1 row)
When I try to run doit.sql: vsql -f doit.sql
I get an error:
ERROR 4856: Syntax error at or near "?" at character 1 LINE 1: ?column?
ERROR 4856: Syntax error at or near "1" at character 2 LINE 1: (1 row)

If I delete this additional string manually the script run correct
Are these additional strings OK? (I must delete them, for example, with bash/python or can they be deleted somehow in the script generate.sql?

marcothesane

You did not add the lines \t and \a to your generator script. They would have removed the ?column? line and the (1 row) line from the report.

maks

marcothesane, thanks a lot!!!