Best Of

Please try the following:

-- Create simple partitioned table
CREATE TABLE t (c INT NOT NULL) PARTITION BY (c);

-- Insert initial data
INSERT INTO t SELECT 201907;

-- Merge partitions
SELECT do_tm_task('mergeout', 't');

-- View initial partition
SELECT partition_key
FROM partitions
WHERE projection_name = 't_super'
ORDER BY partition_key;

-- Update to create new partition
UPDATE t SET c = 202007;

-- Merge updated partitions
SELECT do_tm_task('mergeout', 't');

-- View both partitions
SELECT partition_key
FROM partitions
WHERE projection_name = 't_super'
ORDER BY partition_key;

\echo 'Create stored procedure for dropping partitions'
CREATE OR REPLACE PROCEDURE deletePartition(partition_to_drop INT)
LANGUAGE PLvSQL AS $$
DECLARE
  drop_result VARCHAR(1000);
BEGIN
  SELECT /*+label(drop_partitions_query)*/ DROP_Partitions(
    'public.t',
    partition_to_drop,
    partition_to_drop
  ) INTO drop_result;

  RAISE NOTICE 'DROP_Partitions result: %', drop_result;

EXCEPTION
  WHEN OTHERS THEN
    RAISE EXCEPTION 'Error in deletePartition: %', SQLERRM;
END;
$$;

\echo 'Test case 1: Should succeed - dropping old partition'
CALL deletePartition(201907);

\echo 'View partitions after successful drop'
SELECT partition_key
FROM partitions
WHERE projection_name = 't_super'
ORDER BY partition_key;

\echo 'Test case 2: Should fail - attempting to drop non-existent partition'
CALL deletePartition(201908);

Run time output:

 partition_key
---------------
 201907
 202007
(2 rows)

Create stored procedure for dropping partitions
CREATE PROCEDURE
Test case 1: Should succeed - dropping old partition
vsql:test3.sql:52: NOTICE 2005:  DROP_Partitions result: Partition dropped
 deletePartition
-----------------
               0
(1 row)

View partitions after successful drop
 partition_key
---------------
 202007
(1 row)

Test case 2: Should fail - attempting to drop non-existent partition
vsql:test3.sql:61: NOTICE 2005:  DROP_Partitions result: Partition does not exist
 deletePartition
-----------------
               0
(1 row)

Query 1 and Query 2 are not joining on the same column:

Q1 where
a.netif_unique_id = c.netif_unique_id
and a.netif_unique_id in ('d946b79e-7d9a-4575-91c3-893172f32a5b' )

Q2 where
a.node_unique_id = c.node_unique_id
and a.netif_unique_id in ('d946b79e-7d9a-4575-91c3-893172f32a5b' )

I missed column is_up_to_date in projection systems table.

@scherepanov we are supporting TIME with PARQUET starting 24.3 and it is backported to all versions till 23.4

https://docs.vertica.com/24.3.x/en/sql-reference/statements/copy/parsers/parquet/

Do you have any user created projections on that table? if so, could you please try dropping those projections first?

169.254.169.254 is in AWS a direct link to the EC2 instances metadata. 169.254.0.0/16 is an RFC 1918 network.

this issue is being fixed in major version(24.4). do you need backport for it?

This behavior is not necessarily related to the Vertica JDBC client, as DBeaver, using the same JDBC client, displays arrays in square brackets [], while DbVisualizer uses curly brackets {} for array formatting.

The requested post is attached.

Configure JDBC clients to Work with Your Kerberos-Enabled Vertica Cluster: DbVisualizer, DBeaver, and Others
Posted August 1, 2017 by Sarah Lemaire

@scherepanov there is no need to upgrade to 23.4 or 24.x. you can directly upgrade from 12.0.4 to 24.2 . Please find the doc link which mentions about non sequential upgrades.

https://docs.vertica.com/24.2.x/en/setup/upgrading/