Questions tagged [greenplum]

All versions of greenplum. Add a version-specific tag like greenplum-4.3 if that context is important.

Filter by
Sorted by
Tagged with
0
votes
0answers
46 views

Postgres_fdw Transaction isolation issue

My case I have connected to another GP DB to import data into my PostgreSQL tables and written Java schedulers to refresh it Daily. But when I'm trying to fetch the records everyday by using SQL ...
0
votes
1answer
27 views

Fetching rows from million rows table(Optimization)

I have millions of rows in a table of Greenplum database out of which I have to fetch around 45k rows and store them in a python List. It's taking more than 2hrs to fetch the data. How can I optimize ...
2
votes
1answer
59 views

How to narrow down records in SQL based on conditions

Customer Rank Joining_date salary A 2 2017-10-12 500 A 1 2017-10-10 800 A 1 2017-10-20 400 B ...
0
votes
1answer
108 views

Count days between dates and find largest gap

I need to find all members in our database from a calendar month that do not have a gap of 10+ days between sending data / or in the month total i.e. everyone in April 2018. The table is set up like ...
1
vote
2answers
62 views

Are sql tuning ways same for different DB engine? [closed]

I used Oracle for the half past year and learned some tricks of sql tuning,but now our DB is moving to greenplum and the project manager suggest us to change some of the codes that writted in Oracle ...
0
votes
0answers
29 views

Why might Greenplum get stuck in a loop invoking a web external table?

We have a new table partition maintenance system that went into production today, and it is failing. Each partitioned table has a web external table associated with it, and the shell script that is ...
2
votes
2answers
87 views

Greenplum DB changing table column length

I am new to using Greenplum DB, was working on Oracle DB. I understand conceptually Greenplum in columnar DB and has different workings then Oracle. The question I have is regarding altering table ...
1
vote
1answer
106 views

Can a plpgsql stored procedure monitor for a query and kill it?

I want to write a function that monitors for a particular query becoming a blocker (i.e. it is both blocked by a query, and is blocking another query) and terminate it. Here is my current code, which ...
1
vote
0answers
54 views

Why gather stats on all columns?

Is there a good reason why I should not turn the column statistics down to 0 on columns that are not used in JOIN, WHERE, GROUP BY, or window functons etc.? It strikes me that the default statistics ...
5
votes
1answer
4k views

How to get the sequence name for a serial column

As far as I can tell, this query should show the expression for the new value of a serial column: select d.adsrc from ( SELECT a.attrelid, a.attnum, n.nspname, c.relname, a.attname FROM pg_catalog....
3
votes
0answers
138 views

Why `update` and `delete` acquire EXCLUSIVE lock rather than ROW EXCLUSIVE lock in Greenplum?

As we known, insert,update and delete commands automatically acquire ROW EXCLUSIVE lock in PostgreSQL. As a fork of PostgreSQL 8.2, why in Greenplum update and delete commands automatically acquire ...
1
vote
1answer
48 views

How to retrieve a view column derivation in PostgreSQL or Greenplum

I want to retrieve the derivation expression of a column in a view, and I want to do it dynamically from within a stored procedure, so psql \d+ or pgAdmin is no use to me. Where can I retrieve this ...
0
votes
1answer
263 views

Get partition column for a table in Greenplum

How can I find what the partitioning scheme is for a table? Is it in information_schema or in the pg_class related tables?
1
vote
2answers
361 views

Greenplum : Inserting non-duplicate rows on unique constraint violation

I have a stored procedure that does the following : INSERT INTO schema.my_unique_values SELECT DISTINCT id, value FROM schema.a_huge_table WHERE NOT EXISTS (SELECT 1 ...
2
votes
1answer
73 views

Why would a Greenplum procedure not recognize a view change?

I have a procedure that loops through a query that returns a set of dates, recreates a set of views using the date in the WHERE clause, and the calls a series of other procedures that use these views. ...
2
votes
1answer
318 views

How bad is it to delete from a Greenplum AO table?

We have a few AO tables (which nowadays means "Append Optimized", not "Append Only"). Normally, we just insert into these tables. As part of a significant system change, I need to do a fairly large ...
2
votes
2answers
186 views

Upgrading Greenplum DB that includes PostGIS extension

We've got a number of 4.2.3.2 Greenplum databases that are running PostGIS 1.4. We are trying to get these databases onto a more recent version of Greenplum, such as 4.3.8.1 or the re-released ...
0
votes
1answer
102 views

Partition elimination and date variables

I have a table that is partitioned on a date column, with a default partition. In our deletion process, we drop partitions that are older than the retention date and then delete records that are older ...
1
vote
1answer
61 views

What could cause a text comparison to fail?

DB Version: PostgreSQL 8.2.15 (Greenplum Database 4.3.4.1 build 2) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Feb 10 2015 14:15:10 I'm having a problem in one of our ...
6
votes
2answers
626 views

How can I drop an index on all partitions?

If I create an index on a partitioned table, each partition gets its own index. These are not dropped if I drop the main index. Is there an easy way to do this? I have written a function that looks ...
1
vote
0answers
38 views

When was a table created? [duplicate]

Is there any way to find out when a table was created in PostgreSQL or Greenplum, other than finding the underlying table and looking at the OS creation timestamp?
0
votes
1answer
66 views

Is a partition a relation?

Is a partition a relation in its own right, or is a partitioned table a single relation? Specifically, do we need to increase max_fsm_relations when we split tables up into loads of partitions?
1
vote
3answers
492 views

How to accomplish local joins between facts and dimensions?

A fact table typically contains a composite primary key made up of the keys to all the dimensions that define its granularity. if we distribute the table by this key, it cannot possibly accomplish a ...
1
vote
1answer
1k views

Get total size for all Postgres or Greenplum tables including partitions

Is there an easy way to get the total size of all tables including partitions? I've written this query for Greenplum, but it's a bit round-a-bout: with part_sz as ( select ts.sotdschemaname sch ...
-1
votes
1answer
477 views

Slow select from one billion rows GreenPlum DB

I've created the following table on GreenPlum: CREATE TABLE data."CDR" ( mcc text, mnc text, lac text, cell text, from_number text, to_number text, cdr_time timestamp ...
1
vote
1answer
626 views

How can I add multiple partitions to a Greenplum table using dynamic SQL

I have a function that adds a partition to a table, that works just fine. It takes a schema name, a table name, and a date, and generates and executes an ALTER TABLE statement to add a partition for ...
0
votes
0answers
398 views

How can a select max query return no rows?

I have a process that selects the maximum date from a Greenplum (PostgreSQL 8.2) table, and coalesces it to a default value. Once, and once only, this query returned 0 rows. How is this possible? Even ...
1
vote
1answer
109 views

Why does dynamic SQL behave differently in SELECT FROM than in SELECT in Greenplum? [closed]

We have a function that executes dynamic SQL (I know, SQL injection, the "Bobby Tables" attack, but that's a separate issue). It takes two parameters, interpolates the first into the second, and ...
4
votes
2answers
343 views

Does Greenplum support dynamic sql?

PostgreSQL implements the execute ... using option to pass parameters into dynamic SQL, and as far as I can tell this feature was introduced in version 8.4. We are using Greenplum, which is forked ...
-2
votes
1answer
2k views

How to update values from bytea to varchar in Greenplum?

I need to convert hexa to values and need to update the values in a varchar field, I have tried: select pg_catalog.decode(column,'hex') The above code converted hexa to values but when I try to ...
0
votes
0answers
162 views

Unable to exit a Greenplum SQL script

Is there any way in psql (Greenplum flavour) of exiting a script? I have tried with \q and \quit, e.g. \echo "Hello"; \q -- or \quit but I get: my_database> \i my_script.sql psql:/home/josh/...
1
vote
0answers
336 views

How to make Greenplum 4.2.3 only scan the intended partition?

When I uses unnest() in a View, and uses that View in a select statement, Greenplum seems to fail in only searching for the intended partition and search through all the partition of the main table ...