Rocket® Vertica

Hi, There is inconsistent behaviour of inherited privileges when table is moved to another schema. When table have been created in schema with default inherited privileges, and moved to other schema with inherited privileges, table inherit new schema. When table have been created in schema without default inherited privileges, and moved to other schema with inherited privileges, table do not inherit new schema privileges. Below is an reproducing example. This statement is OK: alter table di1.ti1 set schema di2; Table di2.ti1 changed privilege from SELECT to UPDATE, following changed default privilege between schemas di1 and di2. Problematic statement is alter table de1.te1 set schema di2; It does not result in table di2.te1 having inherited privileges from schema di2. At same time, table di2.te1 is shown in inherited_objects. That makes me think it is actually a bug. I would totally expect that table moved to schema with default privileges will inherit it in any case. Probl

I have a database named DBTest in Eon Mode that was assigned the bucket s3://bucket01 (instance 01 of MinIO) at the time of its creation. However, we want to create another communal storage that points to the bucket s3://bucket02 (instance 02 of MinIO). This is because the information from each MinIO instance belongs to different departments, and we do not want to "mix" the information. Then create a policy to specify that all information from the "test" schema should go to the bucket s3://bucket02. Is it possible to accomplish this? location_path | s3://bucket01/ location_usage | DATA sharing_type | COMMUNAL

I have a table which holds 6 months of data and also partitioned by a Date only column in the table. Is there a way for scheduling DROP_PARTITIONS on vertica from within management console?

Hi, Recently I start observing sessions in Vertica that does not have any audit info, like client OS username, client_OS_hostname, client type, client version etc. Those connections show ancient protocol version 3.5 After some digging, it appears Vertica released driver for VSCode SQLTools. New driver is just rebranding of PgSql driver that was there before, and keeps using old protocol. That is a very poor practice from Vertica. All drivers should provide full audit info, no exception. Please ask client team to update driver, it has to provide all audit fields on connections. Thank you Sergey

I have a stored procedure where, at the conclusion of execution, I must display the elapsed time. To accomplish this, I have created two variables named start_process and end_process. However, upon completion of execution, the value returned by these variables is identical. This issue arose after our upgrade to Vertica 23.3.0-2; such behavior was not observed in version 11.1. Has anything changed, or am I overlooking something? DO $$ DECLARE start_process timestamp; end_process timestamp; y timestamp; BEGIN start_process := select now(); raise info 'start_process: %', start_process; perform SELECT SLEEP(10); end_process := select now(); raise info 'end_process: %', end_process; END; $$ [2024-02-19 18:20:32] [V0002][2005] start_process: 2024-02-19 18:20:22.162039 [2024-02-19 18:20:32] [V0002][2005] end_process: 2024-02-19 18:20:22.162039

Are ranged projections not counted as superprojections, even if they have all columns? The definition of a super-projection: A projection that includes all columns in an anchor table. Vertica uses superprojections to ensure support for all queries and other DML operations. I have ranged projections which have all columns on a table, but they don't aren't listed as is_super_projection, and that means when I add a column to the table, the column doesn't get added to the ranged projection unless specifically requested.

Hi, while running tests on how Vertica (11.1.1-17) behaves when being flooded with small transactions we used dc_tuple_mover_events to monitor TM actions. Now I have some questions to this: -1)What does the column container_count mean? The number of ROS per projection? The number of storage containers? 2) What is actually a storage container? The term is used quite frequently in the documentation, but I didn't find a definiton for it 3) When querying dc_tuple_mover_events I found that the value in container_count stays the same when comparing the "Start" and the "Completed"-event records for the same transaction on the same projection. Is this to be expected?

I'm running v24.1 on EON. We have a number of other Enterprise databases running v12, and I extract from disk_storage daily ("DATA,TEMP") to show growth over time. For EON this view isn't populated with the S3 side of things. How can I extract node usage for communal storage? I can of course look in the S3 vendor's tool, but this is a collection process. Thanks

I'm trying to work out which nodes are buddy pairs, and looking at https://www.vertica.com/kb/KSafetyBestPractices/Content/BestPractices/KSafetyBestPractices.htm In my database I have a number of unsegmented tables as they're very small, so they're obviously on all nodes and the dependencies reflect that. I have K-safety of 1, and on v12. How do I see the dependency 'ring' ?

Hi We have installed Vertica Docker image from "https://hub.docker.com/r/verticadocker/vertica-ce" in our kubernetes environment . How can we access Management Console in this environment ? I could not find reference to MC in Vertica Documentation under Section "Containerized Vertica" . thanks

When I'm going to insert data like this [[1,2,3]] getting error, so what could be nested array data structures in csv

INSERT INTO array_test VALUES (123, '[d, e, r]', '[[1,2,3]]'); in single array data type data has been inserted successfully but in nested array getting error

Synopsis Hello, Currently we're having a small issue, we have a first Kubernetes cluster that hold Vertica setup that works just fine using communal storage on the other hand we need to create a new setup based on the old data, so we did a full backup using vbr.py script which did create all objects into AWS object storage, so far so good, our issue starts when trying to restore data using the right bucket, as the database needs to be shutdown beforehand, in which case the readiness probe fails because of its exec command vsql -w $(cat /etc/podinfo/superuser-passwd) -c 'select 1'. Tried to check if the operator exposes the readiness probe through its CustomRessource verticadbs, but it seems no such property is possible. Thought also about changing manually the readiness probe but again it goes against what the operator stands for with its reconciliation loop. My question is the following: How can we restore the database from a full backup using vbr script on a Kubernetes based setu

Hi, I stumbled about this in vertica.log (both in 11.0.0 as well as 12.0.4; both Eon): when running audit requests, quite some warnings show up ; a00000290857b3 [Txn] Begin Txn: a00000290857b3 'executing audit' a00000290857b3 [EE] **** Could not estimate database size. Switching to exhaustive mode**** a00000290857b3 [Txn] Rollback Txn: a00000290857b3 'executing audit' Sadly neither the documentation nor the KB is of not much help here. Can anyone shed some light on this?

Hi, I've already searched the forum and tried the said 'solutions' but my problem still persists and I'm unable to create a database. I'm using Vertica 10.1 with RHEL8 as a virtual machine and trying to setup a single node. I've added my local IP to /etc/hosts with the proper hostname, installation works fine using the install_script with --hosts flag but as soon as I create a database using adminTools, I get the 'load the startup information from the bootstrap node.' error. I've tried changing the limits of the open file but the changes don't get reflected still shows 1024 with ulimit -n. Below is the adminTools log file. 2021-02-24 12:03:51.169 admintools/10243:0x7f1159bfb700 [at_command.to_subprocess_invocation] <INFO> Command: [/opt/vertica/oss/python3/bin/python3 -m vertica.engine.api.at_runner --no-prompt --module vertica.engine.api.move_staged_files]; input: [#{"staged_file": "/tmp/vstage-073590e5-1ef9-4766-ae2c-94dc609676a4/file", "dest_path": "/opt/vertica/config/d5415

What is the recommended way of moving data from an HBase db which has >1Billion records into vertica

Hi, I found set of tables in prod with very high number of ROS. More checking show that user created unsegmented tables partitioned by date. My first reaction was to add PARTITION GROUP BY and partition_table. I found that PARTITION GROUP BY does not have any effect on ROS for unsegmented tables. Not sure if it is by design, I have not found any reference in docs. If you think this is a defect, please file ticket to development. At least should be mentioned in docs. (I addressed problem by converting tables to segmented). Thank you Sergey

does the customer need to configure SecureData/Voltage to have encryption at rest implemented? I would think any data at rest needs to be encrypted. we did this with oracle using Vormetric. which was at the disk level.

i got alert about vertica 24.1 released yesterday and EOL 1/27 hence valid for 3years we have existing 23.3 valid till 7/26 we have 23.4 valid till 10/26. Are there going to be yearly releases like 25.1 releasing in 1/25 valid for 3 years till 1/28 ? Asking from planning perspective for our clients

With select s3 export we do get desired file url name and row count, but with Vertica version 12 what we have observed that for every query we need to have a new directory and with desired file name we get single file we can't use filesizemb as per the document so there is a possibility that it may produced a big chunk of files 10 cr. Also I dont see the option of using PARTITION over best rather we observed in case of omitting filename, the rows are divied into multiple file chunks based on nodes but the naming convention is not the desired naming convention which we used in SELECT S3 export. We need an alternative to ```SELECT S3EXPORT(* USING PARAMETERS url='s3://bucketname/random/BATCH-09b5c781-055a-4254-8dc7-dfe7c3610469-72994-55-identified.csv', delimiter=',', enclosed_by='"', prepend_hash=false, chunksize='10485760') OVER(PARTITION BEST) FROM (/* subquery /) AS B; WARNING 6619: /+syntactic_join*/ hint omitted, ignoring join hints rows |

Hi, Just checked recent vertica rpm 12.0.4-18. libverticaodbc.so is 165MB! Stripping this file bring size down to 50MB. Still, can you ask dev team, why relatively simple ODBC driver is in file with size bloated that much. vsql comes out of rpm as 11MB, stripping put it down to 5MB. Can you ask build team, why binaries and libraries are not stripped - it increase distribution size at least 3 times. Thank you Sergey

Hi, Vertica COPY command by default ignore 100 errors in single load, either for single record or source. This is legacy of very early Vertica days, when Stonebraker show this feature to prospective clients as one of Vertica selling points. It is actually a very offensive behaviour, especially for beginner users. Vertica by default silently ignore some input data, leaving users very confused. I know about this Vertica feature, and in all load commands I am adding ABORT ON ERROR. I had to explain to beginner users million times what should be done to detect bad syntax on input data and not to ignore errors in input data. Can we address issue - by default COPY command should fail on any error. Please send to product management team suggestion - add database parameter, something like "default_abort_on_error" (default false). If set to true, change COPY command behaviour to fail on any error in input data. That will keep by default compatibility with legacy setting, and allow to change it

Hi, I get a strange error with an Insert query which goes something like:INSERT error: INSERT /*+ DIRECT */ INTO ... then I get Append failed in UDFS File CreateTask: /data//../_node0001_data/Sort_Temp_part_0_45035996385888743_2_0_0_-1.dat No space left on device The Management Console says that disk space usage is low, so I wonder why there is 'No space left on device'. Any idea?

Hi, Vertica did a great job avoiding showing passwords and secrets in clear text. I was surprised when vsql v 12.0.4-11 is showing password in clear text, by checking vsql process on client: myuser@myhost: mydir $ ps -ef|grep vsql myosaccount 7719 2146 0 13:33 pts/0 00:00:00 /opt/vertica/bin/vsql -h mydbhost -U myuser -w -g VerticaExportTable.py -A -t -c connect to vertica mydb user mydbuser password 'passwordincleartext' on .... Vsql nicely masked password in -w. It appears, vsql is not scanning SQL passed with -c for passwords and secrets and not masking them. I think attempt to pass password in vsql var would also not masking passwords and secrets. Please ask security team, are they consider this a problem that need attention. (Yes I know there are more than one workaround) Thank you Sergey

Hi I am using vertica version "Vertica Analytic Database v12.0.4-11" have a table like this CREATE TABLE public.test ( id int, LINKS array[varchar(2048), 250] ); If I try to insert multiple rows in to the above table like this insert into test values (1, '[http://www.somelink123.com/cummins-qsb3-9-engine.html,http://www.facebook.com/]' ), (1, '[http://www.somelink123.com/somenew-link/somenew-link-reallynew-link/somenewlink-link-new.html,http://www.facebook.com/]') getting an error which says 09:29:11 START Executing for: 'Vertica' [Vertica], Schema: public 09:29:11 FAILED [INSERT - 0 rows, 0.683 secs] [Code: 9988, SQL State: 22V02] [Vertica]VJDBC **ERROR: Invalid input syntax for type Varchar(80): "http://www.somelink123.com/somenew-link/somenew-link-reallynew-link/somenewlink-link-new.html" ** strangely although I have defined the type in the array as varchar(2048) I still see varchar(80) for the second row which is having values beyond 80 charecters error