Episode #125 of the Stack Overflow podcast is here. We talk Tilde Club and mechanical keyboards. Listen now
5

In the comments, a_horse_with_no_name said: Are you using Greenplum or Postgres? In Postgres you can just use pg_get_serial_sequence You can find the docs here. pg_get_serial_sequence returns the name of the sequence associated with a column, or NULL if no sequence is associated with the column. The first input parameter is a table name with optional ...


5

I cannot tell for sure, but there is a hint in the CREATE FUNCTION documentation: langname The name of the language that the function is implemented in. May be SQL, C, internal, or the name of a user-defined procedural language. See CREATE LANGUAGE for the procedural languages supported in Greenplum Database. In turn, the referenced place says The ...


5

Greenplum doesn't (as at version 4.3.8) maintain a record in the catalogue linking indexes on partitions to indexes on base tables. The best option is to follow the partitions and find indexes on the partitions matching the definition of the base index. CREATE OR REPLACE FUNCTION drop_child_indexes (index_name varchar) RETURNS VOID AS $functionBody$ DECLARE ...


3

[Originally, the question was tagged with PostgreSQL. I have no idea about Greenplum, but hopefully someone will turn up to answer for that system, too. The rest applies to PostgreSQL only (8.3 onwards, whereas Greenplum was forked from 8.2).] There is a relevant snippet in the documentation: Another important point is that when a server process is ...


3

Here is what i have, its also a little bit roundabout and not testet in PG8.2 (not supported anymore ;) ) but it shows your schema.tablename,PrettySize (first Select) and partitioned Tables are grouped to one (ParentTablename) (second Select). its maybe not the best solution (Tuning welcome) --get all tables sizes and group partitioned tables by ...


3

This is happening because you are trying to alter the table to add partitions when there is a cursor open (active select) on the table. The required locks conflict. You can fix that by first fetching the partition dates into an array, close the cursor and then create partitions from that array. Code sample is below. CREATE OR REPLACE FUNCTION dv_util....


2

To convert an hexadecimal string to characters, the encoding must be specified. This is necessary because the same series of bytes may produce different characters depending on their encoding. For instance, if the hex codes represent an utf-8 string, use: select convert_from(decode(your_hex_string, 'hex'), 'utf-8'); If the encoding is not supported by ...


2

If the security dimension is the largest, then distribute the fact table by the security dimension key. Since it is large, it should have sufficiently entropy on its own to prevent skew. On the other hand, one circumstance when you would not want to do this is if you are frequently selecting with the security dimension in the WHERE clause, and retrieving or ...


2

Your distribution approach by the composite primary key will create a distribution with low or no skew. That's great but, as you point out, it comes with some costs in other areas. In practice even distribution is often a secondary concern. Distribution tends to be by the largest commonly used join or the most common aggregation. In the case you describe ...


2

Not using Greenplum, but guessing from its PostgreSQL heritage it's probably due to the way PL/pgSQL treats SQL statements: like prepared statements. Those are planned and cached on their first use. That means, among other things, that the view is resolved to its underlying tables. The same plan is reused in subsequent iterations, which typically saves time ...


2

The main difference between heap table and AO tables is how the visibility data is stored. In heap tables visibility information is the header of each record, AO tables have a separate visibility map structure. Once there have been UPDATE or DELETE operations on an AO table this visibility map needs to be processed. This will incur a very small addition ...


2

My bet is that the postgis-ossv2.0.3_pv2.0.1_gpdb4.3orca-rhel5-x86_64.gppkg package has a bug and it does not have implemented the postupdate as defined in the Gppkg class. The TAG is optional (SPECFILE_OPTIONAL_TAGS = ... 'postupdate'] ) so it looks like it is calling Check if the upgrade script ($GPHOME/share/postgresql/contrib/postgis-2.0/...


2

From the docs Views in PostgreSQL are implemented using the rule system. So essentially, they get rewritten to simply SELECT statemetns. Nothing in the catalog stores the column expression. Internally, when you run \d on a view, it calls pg_catalog.pg_get_viewdef. You can't go further from that with the system catalog. You're going to have to parse that ...


2

The "tricks" are to convince the DBMS to behave the way you want it to. These are large, complex pieces of software. No two behave exactly the same. So tricks that affect one may or may not work with another. Good practice, such as only retrieving columns that a actually needed, is applicable everywhere.


1

The rank 1 customers and the other customers behave differently, so just use different queries. The last two logic conditions can be computed with a simple ORDER BY per customer; to relate that order back to the entire row requires a window function: WITH Rank1Customers AS ( SELECT DISTINCT Customer FROM MyTable WHERE Rank = 1 ) SELECT Customer, Rank,...


1

(Caveat: I mostly know MySQL.) One of the most basic things varies: Many vendors have a ROWNUM through which a row can be fetched. MySQL's MyISAM uses a byte offset into a file for both PRIMARY and secondary keys. MySQL's InnoDB 'clusters' the PRIMARY KEY with the data, thereby avoiding going through a ROWNUM. Secondary keys are another matter. A side ...


1

It's not even about the type or subtype. Changing a column on a table requires you to drop and recreate all dependencies that reference it. You can do this in a single transaction. CREATE TABLE foo ( a varchar(10) ); CREATE VIEW bar AS TABLE foo; BEGIN; DROP VIEW bar; ALTER TABLE foo ALTER COLUMN a SET DATA TYPE varchar(100); CREATE VIEW bar ...


1

First of all, I think you need to change slightly your query, because the WHERE NOT EXISTS, as it is now written, would be false as soon as your table has some row. You need to specify a WHERE clause: INSERT INTO schema.my_unique_values SELECT DISTINCT id, value FROM schema.a_huge_table a WHERE NOT EXISTS (SELECT 1 ...


1

Got an answer from Pivotal - I should have realised this, but parameterised queries are planned in ignorance of the parameter value, and partition elimination is part of the query plan, so we can't use variables for partition elimination. I am re-tooling our partition elimination to use views that are dynamically constructed with an IN list of dates. This ...


1

This is occurring because something has changed in the way our data is being distributed. Some of our tables are not distributed in the same way as others - the records are on different nodes than the distribution algorithm says that they should be. So, when I run a query that looks for a specific value, the master sends that query only to the node that ...


1

As you're accessing all the columns, then consider row based rather than columnar. Partitioning will help. Pick a column that is easy to divide into around 100 ranges, or that has 100 or so distinct values. As you are not joining to any other tables, you might as well distribute randomly. This may be faster than hashing a text column. Nothing beats trying ...


1

It is failing because the function is executing in the segment* that the row is in, and the segments do not have access to the wider database so it can't select out. * A segment is a node in a distributed database, like a single server in a cluster of servers. The data is spread out over multiple servers, and in a select from a table, a single server runs ...


Only top voted, non community-wiki answers of a minimum length are eligible