Re: [GENERAL] Synchronize filenames in table with filesystem
You can also use LISTEN/NOTIFY. On Tue, Dec 1, 2009 at 6:25 PM, Bret bret_st...@machinemanagement.com wrote: -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Steve Atkins Sent: Tuesday, December 01, 2009 9:44 AM To: pgsql-general General Subject: Re: [GENERAL] Synchronize filenames in table with filesystem On Dec 1, 2009, at 9:19 AM, Ludwig Kniprath wrote: Hi List, not another question on how to store files (db or filesystem), i decided to use the filesystem. I'm now searching for a trigger, that deletes the physical file when deleting a database-record containing the filename in one of its fields. Is there a sample somewhere how this could be done? I'm runnig PG 8.4 on a windows machine. I've done that by having the trigger put the name of the file to be deleted in a to be deleted table. Then an external process polls that table and deletes any file it finds in there (using listen/notify if you need that to happen immediately, but just polling works fine if it's just garbage collection). That has the advantage of not deleting files until the transaction commits too. 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 I'm with Ludwig.. Better to have the database perform it's primary function, and stay away from os chores. -- 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] Postgres Dump out of order
pg_dump dumps data first and then the constraints (including FK) so there shouldn't be any problems when you import the dump. On Wed, Dec 2, 2009 at 2:16 AM, Helio Campos Mello de Andrade helio.cam...@gmail.com wrote: Hi guys, - I'm having a problem when i want to make a backup of my system. - Postgres generated dump was created out of foreing key order and when i try to recreate my database structures, data and functions. Does someone have this same issue? Someone knows of some app that do the job? Regards... -- Helio Campos Mello de Andrade -- 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] Date with time zone
Speaking of timestamps, I think it would be convenient to have a single-word alias for timestamp with time zone. This is the date type I use almost exclusively and its name is annoyingly big. On Sat, Nov 28, 2009 at 6:57 PM, Tom Lane t...@sss.pgh.pa.us wrote: Eduardo Piombino drak...@gmail.com writes: I see current criteria and all the SQL-standard compliance policy, but wouldn't it still make sense to be able to store a date reference, along with a time zone reference? Wouldn't it be useful, wouldn't it be elegant? It seems pretty ill-defined to me, considering that many jurisdictions don't switch daylight savings time at local midnight. How would you know which zone applied on a DST transition date? On the other hand, I don't really see the reasons of this statement: Although the date type *cannot *have an associated time zone, the time type can. Why is this so? Because the SQL committee were smoking something strange that day. You won't find anybody around here who will defend the existence of TIME WITH TIME ZONE. We only put it in for minimal spec compliance. 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 -- 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] Time zone 'GMT+8'
From http://www.postgresql.org/docs/current/static/datatype-datetime.html#DATATYPE-TIMEZONES Another issue to keep in mind is that in POSIX time zone names, positive offsets are used for locations west of Greenwich. On Sun, Nov 29, 2009 at 2:08 AM, seil...@so-net.net.tw wrote: The target pgsql is compiled from 8.4rc1. 'GMT+8' can be found in installed binary file .../share/postgresql/timezone/Etc/GMT+8. This is the recorded script: -BEGIN record-- db1=# select now(); now --- 2009-11-29 14:44:37.322414+08 (1 row) db1=# set timezone to 'GMT+8'; SET db1=# select now(); now --- 2009-11-28 22:45:03.397545-08 (1 row) db1=# set timezone to GMT-8'; SET db1=# select now(); now --- 2009-11-29 14:45:39.160701+08 (1 row) db1=# set timezone to '0'; SET db1=# select now(); now --- 2009-11-29 06:45:54.347482+00 (1 row) -END record-- I thought time zone 'GMT+8' was '8' or UTC+8, and 'GMT-8' was '-8' or UTC-8. Does the original time zone settings shipped with source have special interpretation that is different from my understanding? Regards, CN -- 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] Partitioning table and dynamic SQL in trigger
How about:
IF date_trunc('month',NEW.effective_date)=date_trunc('month',now()) THEN
.
On Tue, Nov 24, 2009 at 7:22 AM, Michal Szymanski dy...@poczta.onet.pl wrote:
In our DB we create partition table for each month (our naming
convetion is follow , and I would like to create trigger that insert
data to appropriate partition table accounting.cdr_y2009m05,
accounting.cdr_y2009m06 etc..).
What is the best solution to create such trigger? I can create trigger
function as follow:
CREATE OR REPLACE FUNCTION partitionig_test RETURNS TRIGGER AS $$
BEGIN
IF NEW.effective_date=DATE '2009-05-01' and
NEW.effective_dateDATE '2009-06-01' THEN
INTO accounting.cdr_y2009m05 VALUES (NEW.*);
ELSE IF
...
but in such solution every month I have modify trigger to handle new
month (during import I have to remember about archive months). Second
solution is to create dynamic SQL query, but every call of
partitionig_test I have to build long string - accounting.cdr_* table
has 50 columns.
Maybe you know third solution, most elegant ?
Michal Szymanski
http://blog.szymanskich.net
--
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] Updating column on row update
MySQL had the following syntax available: `updated_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP I wonder supporting this syntax would speed things up a little bit. Here's a simple benchmark about the situation we are discussing here: There are 2 tables: CREATE TABLE t1 (n integer not null, mtime timestamp with time zone not null); CREATE TABLE t2 (n integer not null, mtime timestamp with time zone not null); and a trigger for the second one: CREATE LANGUAGE plpgsql; CREATE FUNCTION touch() RETURNS trigger AS $$ BEGIN new.mtime := now(); RETURN new; END; $$ LANGUAGE 'plpgsql'; CREATE TRIGGER ttt_mtime BEFORE UPDATE or INSERT ON t2 FOR EACH ROW EXECUTE PROCEDURE touch(); and here's the actual test: test= INSERT INTO t1(n,mtime) SELECT *, now() FROM generate_series(1,100); INSERT 0 100 Time: 7382.313 ms test= INSERT INTO t2(n) SELECT * FROM generate_series(1,100); INSERT 0 100 Time: 24541.088 ms So, updating the column explicitly is 3.5 times faster than the trigger. My guess is that in real life applications where tables have bigger rows (more columns, data types other than integer), the overhead of the trigger will be even smaller. -- 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] using window-functions to get freshest value - how?
SELECT * FROM (SELECT *,row_number() OVER (ORDER BY letztespeicherung DESC) FROM rfmitzeit) t WHERE row_number=1 On Fri, Nov 20, 2009 at 5:31 AM, Massa, Harald Armin c...@ghum.de wrote: id_bf, wert, letztespeicherung: 98, 'blue', 2009-11-09 98, 'red', 2009-11-10 now I have a select to get the youngest value for every id_bf: Not tested: SELECT id_bf, wert, max(letztespeicherung) over (partition by id_bf) FROM rfmitzeit no, that does not work: id_bf;wert;max 98;blue;2009-11-10 00:00:00 98;red;2009-11-10 00:00:00 result is: I get the date of the youngest value. My expected result is: 98;red (that is, the entry of wert that is youngest) thanks for trying, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - %s is too gigantic of an industry to bend to the whims of reality -- 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] using window-functions to get freshest value - how?
oops, I forgot the partition by. Here's the correct query: SELECT * FROM (SELECT *,row_number() OVER (PARTITION BY id_rf ORDER BY letztespeicherung DESC) FROM rfmitzeit) t WHERE row_number=1 You can also do it using SELECT DISTINCT ON: SELECT DISTINCT ON (id_rf) * FROM rfmitzeit ORDER BY id_rf, letztespeicherung DESC; My guess is that the latter will perform better but you should do your own testing. On Fri, Nov 20, 2009 at 5:36 AM, silly silly8...@gmail.com wrote: SELECT * FROM (SELECT *,row_number() OVER (ORDER BY letztespeicherung DESC) FROM rfmitzeit) t WHERE row_number=1 On Fri, Nov 20, 2009 at 5:31 AM, Massa, Harald Armin c...@ghum.de wrote: id_bf, wert, letztespeicherung: 98, 'blue', 2009-11-09 98, 'red', 2009-11-10 now I have a select to get the youngest value for every id_bf: Not tested: SELECT id_bf, wert, max(letztespeicherung) over (partition by id_bf) FROM rfmitzeit no, that does not work: id_bf;wert;max 98;blue;2009-11-10 00:00:00 98;red;2009-11-10 00:00:00 result is: I get the date of the youngest value. My expected result is: 98;red (that is, the entry of wert that is youngest) thanks for trying, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - %s is too gigantic of an industry to bend to the whims of reality -- 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] using window-functions to get freshest value - how?
and how would I use DISTINCT ON for this query? Please bear in mind, that there is more then one id_bf (just stopped the sample data with one of them) I posted the answer more than hour ago: SELECT DISTINCT ON (id_rf) * FROM rfmitzeit ORDER BY id_rf, letztespeicherung DESC; The equivalent with window functions would be: SELECT * FROM (SELECT *,row_number() OVER (PARTITION BY id_rf ORDER BY letztespeicherung DESC) FROM rfmitzeit) t WHERE row_number=1 If you check the query plan you will understand why DISTINCT ON is the best option. Essensially, DISTINCT ON has no additional cost other the cost of ORDER BY id_rf, letztespeicherung DESC which is unavoidable -- 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] Libpq binary mode SELECT ... WHERE ID IN ($1) Question
You could try to prepare a query like this:
select name from foo where id=any($1);
and then pass the array of integers as $1 (although, I don't know how
you can do that as I've never used the C interface of libpq).
On Fri, Nov 13, 2009 at 1:34 AM, Matt Sanchez matt-sanc...@comcast.net wrote:
Hello,
Suppose I have a table:
create table foo ( id int4, name varchar(50) );
I want to prepare and execute in binary mode:
select name from foo where id in ($1);
Execute works when I have a single value for $1, and treat it as a
normal INT4. However, when I attempt to send multiple values for the
parameter, I get no meaningful results.
My code snippets are below.
When setting up an array of numbers to pass as a parameter, is there
something special that needs to be done? The documentation is quite
vague; I did poke around the source and found in contrib some int_array
code, but don't fully understand it.
I suspect I need to do something like ...
(a) when I prepare, do something to tell postgres that I will have an
array of values, instead of a single value, and/or
(b) perhaps encapsulate the array of integers in some manner.
If I could be pointed to an example or documentation, it would be much
appreciated.
Thanks,
Matt Sanchez
The prepare code snippet:
Oid oids[1] = { 23 }; //INT4OID
result = PQprepare( pgconn, getname,
select name from foo where id in ($1)
1, oids );
The execute code snippet:
int ids[4] = { 3, 5, 6, 8 }; // param values we want to setup
/* convert numbers to network byte order ... */
char * paramvalues[1] = (char *) ids;
int paramlengths[1];
int paramformats[1] = { 1 };
paramlengths[0] = = 4 * sizeof( int );
result = PQexecPrepared( pgconn,
getname, // statement name
1, // number of params
paramvalues,
paramlenths,
paramformats,
1 );
--
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] Incremental Backups in postgres
How about using replication instead of incremental backups? On Tue, Nov 10, 2009 at 4:56 PM, Alan Hodgson ahodg...@simkin.ca wrote: On Tuesday 10 November 2009, akp geek akpg...@gmail.com wrote: So Is it always good to have the backup using PG_dump instead of PITR or a combination of both I like to do both. Ongoing PITR, daily base backups (by updating an rsync copy), and weekly pg_dumps that in turn go to tape. PITR gives a very recent restore point in the event of server loss. As previously mentioned, the full (custom) backups let you restore individual tables. They're also a lot smaller than base backups + WAL logs. -- No animals were harmed in the recording of this episode. We tried but that damn monkey was just too fast. -- 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
[GENERAL] error caused by FOREIGN KEY on composite type
I have the following definitions: -- create type mytype as (x integer, y integer); create table foo( a mytype primary key, b integer ); create table bar( a mytype references foo ); insert into foo values((0,0)::mytype,0); -- When I try to do a simple update on foo, I get an error: test= update foo set b=1; ERROR: no conversion function from mytype to record Can someone explain what does this error mean? -- 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] CREATE TABLE LIKE and SERIAL
In 8.4, the sequence value is copied only when INCLUDING DEFAULTS is specified. Otherwise, only the not null constraint is copied. I think this is the most reasonable behavior and I don't see why it should have been explicitly stated in the manual. On Fri, Oct 30, 2009 at 8:44 AM, Mark Morgan Lloyd markmll.pgsql-gene...@telemetry.co.uk wrote: What is the correct behavior of a serial column when a table is created with LIKE? The manual is silent on this. What appears to be happening with 8.2 is that the column in the new table refers to the original sequence generator. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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
[GENERAL] cursor MOVE vs OFFSET in SELECT
Suppose that you have a query, say $sql_query, which is very complicated and produces many rows. Which of the following is going to be faser: $sql_query OFFSET 3000 LIMIT 12; or BEGIN; DECLARE cur1 CURSOR FOR $sql_query; MOVE 3000 IN cur1; FETCH 12 FROM cur1; COMMIT; Naturally, the former cannot be slower than the latter. So my question essentially is whether the MOVE operation on a cursor is (significantly) slower that a OFFSET on the SELECT. -- 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] cursor MOVE vs OFFSET in SELECT
2009/10/26 Grzegorz Jaśkiewicz gryz...@gmail.com: On Mon, Oct 26, 2009 at 10:30 AM, silly silly8...@gmail.com wrote: Suppose that you have a query, say $sql_query, which is very complicated and produces many rows. Which of the following is going to be faser: $sql_query OFFSET 3000 LIMIT 12; or BEGIN; DECLARE cur1 CURSOR FOR $sql_query; MOVE 3000 IN cur1; FETCH 12 FROM cur1; COMMIT; Naturally, the former cannot be slower than the latter. So my question essentially is whether the MOVE operation on a cursor is (significantly) slower that a OFFSET on the SELECT. OFFSET/LIMIT. Afaik cursor always fetches everything. Well, in my experiments they always perform the same. I suspect that the way SELECT/OFFSET is implemented is not much different than cursor/MOVE. -- 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] cursor MOVE vs OFFSET in SELECT
On Mon, Oct 26, 2009 at 10:28 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: silly escribió: 2009/10/26 Grzegorz Jaśkiewicz gryz...@gmail.com: On Mon, Oct 26, 2009 at 10:30 AM, silly silly8...@gmail.com wrote: Suppose that you have a query, say $sql_query, which is very complicated and produces many rows. Which of the following is going to be faser: $sql_query OFFSET 3000 LIMIT 12; or BEGIN; DECLARE cur1 CURSOR FOR $sql_query; MOVE 3000 IN cur1; FETCH 12 FROM cur1; COMMIT; Naturally, the former cannot be slower than the latter. So my question essentially is whether the MOVE operation on a cursor is (significantly) slower that a OFFSET on the SELECT. OFFSET/LIMIT. Afaik cursor always fetches everything. Well, in my experiments they always perform the same. I suspect that the way SELECT/OFFSET is implemented is not much different than cursor/MOVE. The cursor could choose a different plan due to the fast startup behavior that Pavel alludes to. You can actually change that by setting the cursor_tuple_fraction parameter. Whether this plan is faster or slower than the other one is problem dependent. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. OK, so based on what Alvaro Pavel said, the following two possibilities are equivalent as far as the query planner is concerned: $sql_query OFFSET 3000 LIMIT 12; or BEGIN; SET LOCAL cursor_tuple_fraction=1; DECLARE cur1 CURSOR FOR $sql_query; MOVE 3000 IN cur1; FETCH 12 FROM cur1; COMMIT; The problem is that in the latter case, the query planner doesn't know in advance that we are going to skip the first 3000 rows. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
