[GENERAL] PostgreSQL Inheritance and column mapping
Hi, Was there ever any discussion.thought about being able to follow a non-strict by name column mapping between inherited tables and father tables? If for instance someone wants to build an hierarchy on a schema of tables being defined in an established production system, designed with no inheritance in mind, is there any workaround or a way to map the non-common column names, but semantically similar? E.g. Imagine we have the following tables in a legacy non-OO system : create table receipt_document(id serial primary key,doc_no text, date_entered date); create table invoice_document(id serial primary key,invoice_no text, date_entered date, date_due date); . . Then at a (much) later date we decide we want to have an overview of all the documents having to do with purchasing, or even add generic purchase documents for which no special application or structure exists (as of yet) We create the new generic table : create table purchase_document(id serial primary key,doc_no text, date_entered date); And then make this the father table to the two tables with the detailed data : test=# alter table receipt_document INHERIT purchase_document ; -- that works test=# alter table invoice_document INHERIT purchase_document ; ERROR: child table is missing column doc_no Here the problem is that invoice_document lacks col doc_no, which semantically has the same meaning as invoice_no. One work around would be to rename the col and massively replace all occurrences of this in the applications. However i am just wondering if it would be a good idea to extend the way PgSQL inheritance works and have a mapping between columns as well. e.g. somehow denote that purchase_document.doc_no should be merged and mapped with invoice_document.invoice_no. After all, generally speaking invoices have invoice_no's while general docs have doc_no's , right? So I think, the above scenario could be indeed be found a lot of times in systems designed with no OO in mind. -- Achilleas Mantzios Head of IT DEV IT DEPT Dynacom Tankers Mgmt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL Inheritance and column mapping
On 10/2/2014 4:51 AM, Achilleas Mantzios wrote: After all, generally speaking invoices have invoice_no's while general docs have doc_no's , right? So I think, the above scenario could be indeed be found a lot of times in systems designed with no OO in mind. database relations are not 'objects'. -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL Inheritance and column mapping
Achilleas Mantzios ach...@matrix.gatewaynet.com writes: Was there ever any discussion.thought about being able to follow a non-strict by name column mapping between inherited tables and father tables? No. You could use a view with UNION ALL perhaps. (The subtext here is that past discussion of inheritance has generally focused on *eliminating* differences between parent and child tables, so as to make query planning and execution faster/simpler. That is, when the discussion doesn't consist of somebody wanting to get rid of inheritance altogether. I think the enthusiasm for supporting even-less-compatible child tables will be not distinguishable from zero.) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] table versioning approach (not auditing)
Testing that now. Initial results are not looking too performant. I have one single table which had 234575 updates done to it. I am rolling back 13093 of them. It's been running 20 min now, using 100% of a single core, and almost 0 disk. No idea how long it'll run at this point. This is on an i5 desktop with 16 gigs of ram and an ssd. This is a pretty good test though, as it's a real world use case (even if the data was generated with PGBench). We now know that area needs some work before it can be used for anything more than a toy database. Thanks, -Adam On Thu, Oct 2, 2014 at 7:52 AM, Felix Kunde felix-ku...@gmx.de wrote: Hey there Thanks again for the fix. I was able to merge it into my repo. Also thanks for benchmarking audit. Very interesting results. I wonder how the recreation of former database states scales when processing many deltas. Haven’t done a lot of testing in that direction. I will transfer the code soon to a more public repo on GitHub. As far as I see I have to create an organization for that. Cheers Felix *Gesendet:* Mittwoch, 01. Oktober 2014 um 17:09 Uhr *Von:* Adam Brusselback adambrusselb...@gmail.com *An:* Felix Kunde felix-ku...@gmx.de *Cc:* pgsql-general@postgresql.org pgsql-general@postgresql.org *Betreff:* Re: [GENERAL] table versioning approach (not auditing) I know we're kinda hijacking this thread, so sorry for that. If you'd like to do that, i'd be more than happy to use it and push any fixes / changes upstream. I don't have much of a preference on the name either, as long as it's something that makes sense. I would consider myself far from an expert though! Either way, more people using a single solution is a good thing. As a side note, I did some benchmarking this morning and wanted to share the results: pgbench -i -s 140 -U postgres pgbench pgbench -c 4 -j 4 -T 600 -U postgres pgbench no auditing tps: 2854 NOTE: Accounts are audited auditing tps: 1278 pgbench -c 2 -j 2 -N -T 300 -U postgres pgbench no auditing tps: 2504 NOTE: Accounts are audited auditing tps: 822 pgbench -c 2 -j 2 -T 300 -U postgres pgbench no auditing tps: 1836 NOTE: branches and tellers are audited, accounts are not auditing tps: 505 I'd love to see if there are some easy wins to boost the performance. On Wed, Oct 1, 2014 at 5:19 AM, Felix Kunde felix-ku...@gmx.de wrote: Hey there. Thank you very much for that fix! Thats why I'd like to have a joint development and joint testing. It's way more convincing for users to go for a solution that is tested by some experts than just by a random developer :) I'm open to create a new project and push the code there. Don't care about the name. Then we might figure out which parts are already good, which parts could be improved and where to go next. I think switching to JSONB for example will be easy, as it offers the same functions than JSON afaik. Gesendet: Dienstag, 30. September 2014 um 21:16 Uhr Von: Adam Brusselback adambrusselb...@gmail.com An: Felix Kunde felix-ku...@gmx.de Cc: pgsql-general@postgresql.org pgsql-general@postgresql.org Betreff: Re: [GENERAL] table versioning approach (not auditing) Felix, I'd love to see a single, well maintained project. For example, I just found yours, and gave it a shot today after seeing this post. I found a bug when an update command is issued, but the old and new values are all the same. The trigger will blow up. I've got a fix for that, but if we had one project that more than a handful of people used, stuff like that would be quashed very quickly. I love the design of it by the way. Any idea what it will take to move to JSONB for 9.4? On Tue, Sep 30, 2014 at 7:22 AM, Felix Kunde felix-ku...@gmx.de wrote:Hey yes i'm adding an additional key to each of my tables. First i wanted to use the primary key as one column in my audit_log table, but in some of my tables the PK consists of more than one column. Plus it's nice to have one key that is called the same over all tables. To get a former state for one row at date x I need to join the latest delta BEFORE date x with each delta AFTER date x. If I would log complete rows, this joining part would not be neccessary, but as I usually work with spatial databases that have complex geometries and also image files, this strategy is too harddisk consuming. If there are more users following a similar approach, I wonder why we not throw all the good ideas together, to have one solution that is tested, maintained and improved by more developpers. This would be great. Felix Gesendet: Montag, 29. September 2014 um 23:25 Uhr Von: Abelard Hoffman abelardhoff...@gmail.com[abelardhoff...@gmail.com ] An: Felix Kunde felix-ku...@gmx.de[felix-ku...@gmx.de] Cc: pgsql-general@postgresql.org[pgsql-general@postgresql.org] pgsql-general@postgresql.org[pgsql-general@postgresql.org] Betreff: Re: [GENERAL] table versioning approach (not auditing) Thank you Felix, Gavin,
Re: [GENERAL] table versioning approach (not auditing)
Ended up running for 28 min, but it did work as expected. On Thu, Oct 2, 2014 at 10:27 AM, Adam Brusselback adambrusselb...@gmail.com wrote: Testing that now. Initial results are not looking too performant. I have one single table which had 234575 updates done to it. I am rolling back 13093 of them. It's been running 20 min now, using 100% of a single core, and almost 0 disk. No idea how long it'll run at this point. This is on an i5 desktop with 16 gigs of ram and an ssd. This is a pretty good test though, as it's a real world use case (even if the data was generated with PGBench). We now know that area needs some work before it can be used for anything more than a toy database. Thanks, -Adam On Thu, Oct 2, 2014 at 7:52 AM, Felix Kunde felix-ku...@gmx.de wrote: Hey there Thanks again for the fix. I was able to merge it into my repo. Also thanks for benchmarking audit. Very interesting results. I wonder how the recreation of former database states scales when processing many deltas. Haven’t done a lot of testing in that direction. I will transfer the code soon to a more public repo on GitHub. As far as I see I have to create an organization for that. Cheers Felix *Gesendet:* Mittwoch, 01. Oktober 2014 um 17:09 Uhr *Von:* Adam Brusselback adambrusselb...@gmail.com *An:* Felix Kunde felix-ku...@gmx.de *Cc:* pgsql-general@postgresql.org pgsql-general@postgresql.org *Betreff:* Re: [GENERAL] table versioning approach (not auditing) I know we're kinda hijacking this thread, so sorry for that. If you'd like to do that, i'd be more than happy to use it and push any fixes / changes upstream. I don't have much of a preference on the name either, as long as it's something that makes sense. I would consider myself far from an expert though! Either way, more people using a single solution is a good thing. As a side note, I did some benchmarking this morning and wanted to share the results: pgbench -i -s 140 -U postgres pgbench pgbench -c 4 -j 4 -T 600 -U postgres pgbench no auditing tps: 2854 NOTE: Accounts are audited auditing tps: 1278 pgbench -c 2 -j 2 -N -T 300 -U postgres pgbench no auditing tps: 2504 NOTE: Accounts are audited auditing tps: 822 pgbench -c 2 -j 2 -T 300 -U postgres pgbench no auditing tps: 1836 NOTE: branches and tellers are audited, accounts are not auditing tps: 505 I'd love to see if there are some easy wins to boost the performance. On Wed, Oct 1, 2014 at 5:19 AM, Felix Kunde felix-ku...@gmx.de wrote: Hey there. Thank you very much for that fix! Thats why I'd like to have a joint development and joint testing. It's way more convincing for users to go for a solution that is tested by some experts than just by a random developer :) I'm open to create a new project and push the code there. Don't care about the name. Then we might figure out which parts are already good, which parts could be improved and where to go next. I think switching to JSONB for example will be easy, as it offers the same functions than JSON afaik. Gesendet: Dienstag, 30. September 2014 um 21:16 Uhr Von: Adam Brusselback adambrusselb...@gmail.com An: Felix Kunde felix-ku...@gmx.de Cc: pgsql-general@postgresql.org pgsql-general@postgresql.org Betreff: Re: [GENERAL] table versioning approach (not auditing) Felix, I'd love to see a single, well maintained project. For example, I just found yours, and gave it a shot today after seeing this post. I found a bug when an update command is issued, but the old and new values are all the same. The trigger will blow up. I've got a fix for that, but if we had one project that more than a handful of people used, stuff like that would be quashed very quickly. I love the design of it by the way. Any idea what it will take to move to JSONB for 9.4? On Tue, Sep 30, 2014 at 7:22 AM, Felix Kunde felix-ku...@gmx.de wrote:Hey yes i'm adding an additional key to each of my tables. First i wanted to use the primary key as one column in my audit_log table, but in some of my tables the PK consists of more than one column. Plus it's nice to have one key that is called the same over all tables. To get a former state for one row at date x I need to join the latest delta BEFORE date x with each delta AFTER date x. If I would log complete rows, this joining part would not be neccessary, but as I usually work with spatial databases that have complex geometries and also image files, this strategy is too harddisk consuming. If there are more users following a similar approach, I wonder why we not throw all the good ideas together, to have one solution that is tested, maintained and improved by more developpers. This would be great. Felix Gesendet: Montag, 29. September 2014 um 23:25 Uhr Von: Abelard Hoffman abelardhoff...@gmail.com[ abelardhoff...@gmail.com] An: Felix Kunde felix-ku...@gmx.de[felix-ku...@gmx.de] Cc:
[GENERAL] Getting my Database name in a C Extension
Hi, I'm writing an extention (FDW), and I need, in my C code, the name of my database (contrib_regression_test for example), and I've two questions: 1) What is the easiest way to get that directly in C? 2) Is there a way to get this information in the SQL extension installation/update scripts (like the @/extschema/@ substitution)? Thanks, Cedric -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting my Database name in a C Extension
Cedric Berger ced...@precidata.com writes: I'm writing an extention (FDW), and I need, in my C code, the name of my database (contrib_regression_test for example), and I've two questions: 1) What is the easiest way to get that directly in C? The usual locution is get_database_name(MyDatabaseId). 2) Is there a way to get this information in the SQL extension installation/update scripts (like the @/extschema/@ substitution)? Nope. Doesn't seem to me like a remarkably good idea to refer to it in that sort of way anyway. What would happen if someone renamed the database after the extension is installed? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Creating a PL/pgSQL function that returns multiple out parameters and refcursor
Hi Ho do I create a PL/pgSQL function that returns multiple out parameters and a refcursor. Something like this: create function myfunction(p_cursor out refcursor, p_code out varchar, p_message out varchar) returns record as $$ begin open p_cursor is select * from table; p_code := 'OK'; p_message := 'message'; return ??? end; $$ language plpgsql;
Re: [GENERAL] Postgres tcp_keepalive_xxxx parameters.
Hi Tom, First of all, thank you for your fast response! I did not try to set up keepalives in client yet. This is a good point. We are using JDBC to connect to PG from JBoss application server. I have seen the JDBC documentation and keepalive can be enabled when creating the connection to the database: http://jdbc.postgresql.org/documentation/head/connect.html Unfortunately seems to be tricky to enable it because I guess it implies to change the Java code. In that case it is not straight forward. I do not know if there is a configuration file to set up this. I am going to look for it. By the way, can you confirm me if keepalive behaves like I stated in my previous update...? Thanks and best regards, JL -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: miércoles, 01 de octubre de 2014 17:04 To: José Luis Rando Calvo Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Postgres tcp_keepalive_ parameters. jlrando jose.luis.rando.ca...@ericsson.com writes: We have an issue with postgres clients tha are being disconnected from database server after some time. Client is a web application which creates a pool of connections. Since client and server are on different VLANS I think the problem is that the FW which is routing traffic is droping idle connections after some time. Probably. It might be asymmetric; have you tried enabling keepalives from the client end, rather than the server? If using libpq, you can set keepalive parameters in the connection string: http://www.postgresql.org/docs/9.3/static/libpq-connect.html#LIBPQ-PARAMKEYWORDS If that doesn't fix it, you might want to get out Wireshark or a similar tool and verify that keepalive packets are actually getting sent. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Benching Queries
Does anyone have a good solution for benching queries under various conditions, and collecting the EXPLAIN data ? I looked at pgbench, but it doesn't seem to be what I want. My situation is this- - For a given query, there are 3-5 different ways that I can run it. - Each form of the query has a completely different execution plan and query time, often using different indexes. - The same query runs differently on first query, vs subsequent queries (when the indexes/tables are already in memory). My goal is to find an overall balance of query time (cold-start vs in-memory) and indexes (number of). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to find greatest record before known values fast
I’m looking for a way to increase select statement speed in Postgres 9.0.
Table has required index present. Desired result can obtained using index
(kuupaev,kellaaeg) immediately.
However Postgres scans all rows:
explain analyze SELECT
max( kuupaev||kellaaeg ) as res
from firma2.ALGSA
where laonr=1 and kuupaev =current_date and
(kuupaev,kellaaeg) = ( current_date, '23 59' )
Aggregate (cost=6932.65..6932.67 rows=1 width=10) (actual
time=1608.590..1608.592 rows=1 loops=1)
- Seq Scan on algsa (cost=0.00..6571.49 rows=144464 width=10) (actual
time=0.032..922.431 rows=144458 loops=1)
Filter: ((laonr = 1::numeric) AND (kuupaev = ('now'::text)::date)
AND (ROW(kuupaev, kellaaeg) = ROW(('now'::text)::date, '23 59'::bpchar)))
Total runtime: 1608.846 ms
In real query instead of 1, current_date and '23 59' there are variable
parameters.
Table has both indexes present but postgres will not use them.
Indexes can changed and query can re-written if this helps.
Table structure cannot changed. char columns cannot replaced with varchar
columns. kuupaev must be date and kellaaeg must be char(5) type.
Query contains reduntant condition `kuupaev =current_date` but index is still
not used.
I tried also `SELECT max( (kuupaev,kellaaeg ))` but got error that max()
function does not exist.
How to speed this query ?
Table structure is :
CREATE TABLE firma2.algsa
(
id serial NOT NULL,
laonr numeric(2,0),
kuupaev date NOT NULL,
kellaaeg character(5) NOT NULL DEFAULT ''::bpchar,
osak character(10) NOT NULL,
toode character(20) NOT NULL,
partii character(15),
kogus numeric(12,4) NOT NULL DEFAULT 0,
hind numeric(15,5) NOT NULL DEFAULT 0,
kulum numeric(15,5) NOT NULL DEFAULT 0,
tegkogus numeric(12,4),
stkuupaev date,
klient character(12),
masin character(5),
CONSTRAINT algsa_pkey PRIMARY KEY (id)
);
CREATE INDEX algsa_kuupaev_idx
ON firma2.algsa
USING btree
(kuupaev);
CREATE INDEX algsa_kuupaev_kellaaeg_idx
ON firma2.algsa
USING btree
(kuupaev, kellaaeg);
using
PostgreSQL 9.0.3, compiled by Visual C++ build 1500, 32-bit
Posted also in
http://stackoverflow.com/questions/26165745/how-find-greatest-tuple-before-given-2-column-tuple-in-postgres-fast
Andrus.
[GENERAL] Creating index on concatenated char columns fails is Postgres 9 (regression)
Steps to reproduce: Run commands create temp table test (kuupaev date, kellaaeg char(5) ) on commit drop; create index test on test ((kuupaev||kellaaeg)); in PostgreSQL 9.3.4, compiled by Visual C++ build 1600, 32-bit Observed result: ERROR: functions in index expression must be marked IMMUTABLE In PostgreSQL 8.4.4, compiled by Visual C++ build 1400, 32-bit those commands work OK. I need this index to speed up query SELECT max( kuupaev||kellaaeg ) from ALGSA where laonr=?nlaonr and kuupaev =?prmLOPP and kuupaev||kellaaeg = ?someparam How to fix or other way to speed this query? Posted also in http://stackoverflow.com/questions/26161561/how-to-create-composite-index-in-postgres-9 Andrus.
[GENERAL] Getting my Database name in a C Extension
Hi, I'm writing an extention (FDW), and I need, in my C code, the name of my database (contrib_regression_test for example), and I've two questions: 1) What is the easiest way to get that directly in C? 2) Is there a way to get this information in the SQL extension installation/update scripts (like the @/extschema/@ substitution)? Thanks, -- Cedric Berger Precidata Sarl Maladière 71c 2000 Neuchâtel ced...@precidata.com 032 930 29 62 079 934 11 02 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] installing on mac air development machine
we've always installed on linux so need help with a new mac air running latest osx in the instructions it shows several methods: 1) enterprisedb (but this does not look open source ?) 2) fink 3) macports 4) source etc what do most use ? thks ps: is there a mac build for 9.4 beta 3 yet ? we would use that if available and not too hard to install -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting my Database name in a C Extension
On 10/2/2014 8:11 AM, Cedric Berger wrote: I'm writing an extention (FDW), and I need, in my C code, the name of my database (contrib_regression_test for example), and I've two questions: 1) What is the easiest way to get that directly in C? 2) Is there a way to get this information in the SQL extension installation/update scripts (like the @/extschema/@ substitution)? you could SELECT CURRENT_CATALOG; using the SPI_ functions from your C, or directly in your SQL extension script... this returns the database name (the name is from the sql standard). see http://www.postgresql.org/docs/current/static/functions-info.html for more info functions like this available in SQL. -- john r pierce 37N 122W somewhere on the middle of the left coast
Re: [GENERAL] installing on mac air development machine
On 14/10/03 7:50, john.tiger wrote: we've always installed on linux so need help with a new mac air running latest osx in the instructions it shows several methods: 1) enterprisedb (but this does not look open source ?) 2) fink 3) macports 4) source etc what do most use ? thks There are three main package management systems for OS X - Fink, MacPorts and Homebrew. Most people swear by one and swear at the others. If you want a more Linux-like package management experience, one of these will be the way to go; if you just need PostgreSQL up and running, one of the binary application installers may be easier to manage. There's a useful overview of options here in case you haven't already seen it: http://www.postgresql.org/download/macosx/ Personally I build from source for development work and Macports for general package management. ps: is there a mac build for 9.4 beta 3 yet ? we would use that if available and not too hard to install beta3 has not yet been released (October 9th is the scheduled date). Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] installing on mac air development machine
The GUI installer for Mac OS X downloaded from postgresql.org works fine. ~ john On Oct 2, 2014, at 3:50 PM, john.tiger john.tigernas...@gmail.com wrote: we've always installed on linux so need help with a new mac air running latest osx in the instructions it shows several methods: 1) enterprisedb (but this does not look open source ?) 2) fink 3) macports 4) source etc what do most use ? thks ps: is there a mac build for 9.4 beta 3 yet ? we would use that if available and not too hard to install -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] installing on mac air development machine
On Oct 2, 2014, at 7:30 PM, john gale wrote: The GUI installer for Mac OS X downloaded from postgresql.org works fine. Unless you NEED to use the source/etc version, use the GUI installer. Unless you are already on a system where installing from Fink/Macports/Source is commonplace... you're going to spend more time installing and configuring the environment than you will using the application. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] installing on mac air development machine
On 10/02/2014 03:50 PM, john.tiger wrote: we've always installed on linux so need help with a new mac air running latest osx in the instructions it shows several methods: 1) enterprisedb (but this does not look open source ?) It is just the community version of Postgres behind a graphical installer, so yes it is open source. -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Creating index on concatenated char columns fails is Postgres 9 (regression)
On 10/02/2014 01:49 PM, Andrus wrote: Steps to reproduce: Run commands create temp table test (kuupaev date, kellaaeg char(5) ) on commit drop; create index test on test ((kuupaev||kellaaeg)); in PostgreSQL 9.3.4, compiled by Visual C++ build 1600, 32-bit Observed result: ERROR: functions in index expression must be marked IMMUTABLE In PostgreSQL 8.4.4, compiled by Visual C++ build 1400, 32-bit those commands work OK. I need this index to speed up query SELECT max( kuupaev||kellaaeg ) from ALGSA where laonr=?nlaonr and kuupaev =?prmLOPP and kuupaev||kellaaeg = ?someparam How to fix or other way to speed this query? My guess is you are seeing the result of this commit: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5a86e5e1930d95f495a134000512d6ca22064338 which was back ported to 8.x but after 8.4.4. As I recall there has been a general tightening up of checks for VOLATILE vs IMMUTABLE. For a possible solution see here: http://dba.stackexchange.com/questions/71133/creating-unique-constraint-to-be-validated-from-input Posted also in http://stackoverflow.com/questions/26161561/how-to-create-composite-index-in-postgres-9 Andrus. -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to find greatest record before known values fast
So kellaaeg is a time? Your best bet here would be to create an index that is
an actual timestamp comprised of both kuupaev and kellaaeg. You could do this
with to_timestamp by concatinating both fields together, or it may be easier to
replace the space in kellaaeg with a colon and cast it to time, then add the
two:
kuupaev + replace( kellaaeg, ' ', ':' )::time
I know you can't alter the table, but can you create a view on top of the
table? If you did that, you could have a real timestamp field in the view that
is calculated from kuupaev and kellaaeg and you can create a functional index
that uses the same calculation. That would be the easiest way to use this.
On 10/2/14, 3:49 PM, Andrus wrote:
I’m looking for a way to increase select statement speed in Postgres 9.0.
Table has required index present. Desired result can obtained using index
(kuupaev,kellaaeg) immediately.
However Postgres scans all rows:
explain analyze SELECT
max( kuupaev||kellaaeg ) as res
from firma2.ALGSA
where laonr=1 and kuupaev =current_date and
(kuupaev,kellaaeg) = ( current_date, '23 59' )
Aggregate (cost=6932.65..6932.67 rows=1 width=10) (actual
time=1608.590..1608.592 rows=1 loops=1)
- Seq Scan on algsa (cost=0.00..6571.49 rows=144464 width=10) (actual
time=0.032..922.431 rows=144458 loops=1)
Filter: ((laonr = 1::numeric) AND (kuupaev = ('now'::text)::date) AND
(ROW(kuupaev, kellaaeg) = ROW(('now'::text)::date, '23 59'::bpchar)))
Total runtime: 1608.846 ms
In real query instead of 1, current_date and '23 59' there are variable
parameters.
Table has both indexes present but postgres will not use them.
Indexes can changed and query can re-written if this helps.
Table structure cannot changed. char columns cannot replaced with varchar
columns. kuupaev must be date and kellaaeg must be char(5) type.
Query contains reduntant condition `kuupaev =current_date` but index is still
not used.
I tried also `SELECT max( (kuupaev,kellaaeg ))` but got error that max()
function does not exist.
How to speed this query ?
Table structure is :
CREATE TABLE firma2.algsa
(
id serial NOT NULL,
laonr numeric(2,0),
kuupaev date NOT NULL,
kellaaeg character(5) NOT NULL DEFAULT ''::bpchar,
osak character(10) NOT NULL,
toode character(20) NOT NULL,
partii character(15),
kogus numeric(12,4) NOT NULL DEFAULT 0,
hind numeric(15,5) NOT NULL DEFAULT 0,
kulum numeric(15,5) NOT NULL DEFAULT 0,
tegkogus numeric(12,4),
stkuupaev date,
klient character(12),
masin character(5),
CONSTRAINT algsa_pkey PRIMARY KEY (id)
);
CREATE INDEX algsa_kuupaev_idx
ON firma2.algsa
USING btree
(kuupaev);
CREATE INDEX algsa_kuupaev_kellaaeg_idx
ON firma2.algsa
USING btree
(kuupaev, kellaaeg);
using
PostgreSQL 9.0.3, compiled by Visual C++ build 1500, 32-bit
Posted also in
http://stackoverflow.com/questions/26165745/how-find-greatest-tuple-before-given-2-column-tuple-in-postgres-fast
Andrus.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL Inheritance and column mapping
On 10/2/14, 9:00 AM, Tom Lane wrote: Achilleas Mantzios ach...@matrix.gatewaynet.com writes: Was there ever any discussion.thought about being able to follow a non-strict by name column mapping between inherited tables and father tables? No. You could use a view with UNION ALL perhaps. FWIW, I've had some less than stellar results with that (admittedly, back on 8.4). The other thing you could do is something like: ALTER TABLE invoice_document RENAME TO invoice_document_raw; ALTER TABLE invoice_document_raw RENAME invoice_no TO doc_no; CREATE VIEW invoice_document AS SELECT ... , doc_no AS invoice_no , ... FROM invoice_document_raw ; If you make that view writable then no one needs to know that you renamed the column in the underlying table. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to find greatest record before known values fast
Andrus Moor wrote Table has both indexes present but postgres will not use them. Indexes can changed and query can re-written if this helps. Table structure cannot changed. char columns cannot replaced with varchar columns. kuupaev must be date and kellaaeg must be char(5) type. Don't discard the option to add additional trigger-maintained columns to the table and then modify the where clause to use those new columns while still outputting the originals. You also need to explain whether you can update from 9.0.3 to 9.0.18 As Craig Ringer already said working with seldom used types in unusual ways on ancient releases means that you may be stuck with second rate workarounds or just living with the performance degradation. I guess at worse you could delete data so the scan doesn't take as long... David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-find-greatest-record-before-known-values-fast-tp5821461p5821523.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] installing on mac air development machine
On 10/2/2014 4:37 PM, Adrian Klaver wrote: On 10/02/2014 03:50 PM, john.tiger wrote: we've always installed on linux so need help with a new mac air running latest osx in the instructions it shows several methods: 1) enterprisedb (but this does not look open source ?) It is just the community version of Postgres behind a graphical installer, so yes it is open source. postgres is of course open source. the enterprisedb installer I'm less sure of, but its free to use. if you just need postgres running while you're doing software development, the postgresql.app version may be the simplest to use. you run it on the desktop and postgres is running. close it and its not. your user id owns the pgdata and the process, so you don't have to jump through sudo hoops to edit the config files. http://postgresapp.com/ (Caveat: I don't own a mac) -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to find greatest record before known values fast
Jim Nasby jim.na...@bluetreble.com writes:
So kellaaeg is a time? Your best bet here would be to create an index
that is an actual timestamp comprised of both kuupaev and kellaaeg.
The real problem with this query, or at least with the index design,
is that the index design isn't accounting for the need to constrain
laonr. The best way to create the index is with laonr first, and
instead of the max() write something like
regression=# create table foo (f1 int, f2 date, f3 bpchar(5));
CREATE TABLE
regression=# create index on foo (f1, f2, f3);
CREATE INDEX
regression=# explain select * from foo where f1 = 1 and (f2,f3) =
(current_date, '23 59') order by f2 desc, f3 desc limit 1;
QUERY PLAN
---
Limit (cost=0.01..5.44 rows=1 width=17)
- Index Scan Backward using foo_f1_f2_f3_idx on foo (cost=0.01..16.32
rows=3 width=17)
Index Cond: ((f1 = 1) AND (ROW(f2, f3) = ROW(('now'::text)::date, '23
59'::bpchar)))
(3 rows)
(tested on 9.0.18, should work on newer versions too)
BTW, the fact that newer versions are refusing to create an index on
kuupaev||kellaaeg should not be dismissed as mere pedantry.
The reason for that is that this expression involves a cast from
date to text, and the format of the text you get depends on DateStyle.
And that should call your attention to the fact that *the original query
gives the wrong answer*, or at least an answer that I bet is not the one
you want, unless DateStyle chances to be ISO.
IMO, one of the ten deadly sins of database design is using text-string
mashing to accomplish operations that are not naturally textual. This
query and the table design itself are in dire need of sackcloth and ashes.
Had the table designer had the wit to use a timestamp field rather than
this unholy mashup, we'd not be having this conversation.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] installing on mac air development machine
On Oct 2, 2014, at 8:04 PM, John R Pierce pie...@hogranch.com wrote: On 10/2/2014 4:37 PM, Adrian Klaver wrote: On 10/02/2014 03:50 PM, john.tiger wrote: we've always installed on linux so need help with a new mac air running latest osx in the instructions it shows several methods: 1) enterprisedb (but this does not look open source ?) It is just the community version of Postgres behind a graphical installer, so yes it is open source. postgres is of course open source. the enterprisedb installer I'm less sure of, but its free to use. if you just need postgres running while you're doing software development, the postgresql.app version may be the simplest to use. you run it on the desktop and postgres is running. close it and its not. your user id owns the pgdata and the process, so you don't have to jump through sudo hoops to edit the config files. http://postgresapp.com/ (Caveat: I don't own a mac) I do, and use postgres.app to develop against - and you're right. postgres.app is a trivial install, and it works beautifully for development using postgresql. It isn't really a desktop app, it's a tiny GUI controller that lives in your menu bar and controls a fairly standard postgresql installation under the covers. It can start up and shut down as you log in and log out, or you can start and stop it manually. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Creating a PL/pgSQL function that returns multiple out parameters and refcursor
On Fri, Oct 3, 2014 at 1:15 AM, Néstor Boscán nesto...@gmail.com wrote: Ho do I create a PL/pgSQL function that returns multiple out parameters and a refcursor. Using a plain RETURN; is just but fine, your output parameters are being set internally in the function: =# create function myfunction(id_base in int, id1 out int, id2 out varchar) returns record as $$ begin id1 := id_base * 2; id2 := id_base * 3; return; end; $$ language plpgsql; CREATE FUNCTION =# select * from myfunction(3); id1 | id2 -+- 6 | 9 (1 row) =# select * from myfunction(4); id1 | id2 -+- 8 | 12 (1 row) Regards, -- Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
