Re: [GENERAL] [SQL] function to find difference between in days between two dates
Hello PostgreSQL hasn't any official function for it. If you need it, you can write own function CREATE FUNCTION date_diff(date, date) returns integer as $$ select $1-$2; $$ language sql; Regards Pavel Stehule 2007/6/14, Ashish Karalkar <[EMAIL PROTECTED]>: Hello all, Is there any function to find differences in days between two dates? I am using select abs(current_date - '2007-06-15') to get the desired result. but I think there must be a function and I am missing it, if so, can anybody please point me to that. Thanks in advance With regards Ashish Karalkar ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] function to find difference between in days between two dates
Hello all, Is there any function to find differences in days between two dates? I am using select abs(current_date - '2007-06-15') to get the desired result. but I think there must be a function and I am missing it, if so, can anybody please point me to that. Thanks in advance With regards Ashish Karalkar
Re: [GENERAL] pointer to feature comparisons, please
The DELETE should block, no? Why ? Foreign keys put an ON DELETE trigger on the referenced table Foreign keys that silently, automatic DELETE records? Did I read that correctly? Isn't that the point of ON DELETE CASCADE ? besides checking the referencing column on insert/update... If you just implement a constraint, you only get half the functionality. But when I define a FK *constraint*, that's all I *want*! When you add a REFERENCE foo( bar ) foreign key and don't specify ON DELETE clause, I believe you get ON DELETE NO ACTION, which means the referenced row can't be deleted if there are referencing rows... so when you have a FK constraint, it actually constrains two tables, and pg uses share locking to ensure race conditions can't cause an inconsistent database. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] [SQL] setof or array as input parameter to postgresql 8.2 functions
How can delete my suscription to this forums? -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] En nombre de Jyoti Seth Enviado el: jueves, 14 de junio de 2007 8:04 Para: 'Pavel Stehule' CC: pgsql-general@postgresql.org; [EMAIL PROTECTED] Asunto: Re: [SQL] setof or array as input parameter to postgresql 8.2 functions Thanks a lot. Regards, Jyoti -Original Message- From: Pavel Stehule [mailto:[EMAIL PROTECTED] Sent: Thursday, June 14, 2007 11:27 AM To: Jyoti Seth Cc: pgsql-general@postgresql.org; [EMAIL PROTECTED] Subject: Re: [SQL] setof or array as input parameter to postgresql 8.2 functions 2007/6/14, Jyoti Seth <[EMAIL PROTECTED]>: > Thanks for the solution. With this I am able to pass arrays and > multidimensional arrays in postgresql functions. > > One of my problem is still left I want to pass set of values with different > datatypes.For eg: > I want to pass following values to the function: > 1 ajay 1000.12 > 2 rita 2300.24 > 3 leena 1230.78 > 4 jaya 3432.45 > > As the values have different data types I have to create three > different arrays. Is there any way with which I can pass this as a > single setof values. > > You have to wait for 8.3 where arrays on composite types are supported. Currently in one our application we use array of arrays where different types are too, and we cast it to text. Regards Pavel ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Regular expressions in procs
Steve Manes wrote: > I apologize if I'm having a rookie brain block, but is there a way to > massage a string inside a proc to, for instance, strip it of all > non-alpha characters using a regular expression? SELECT regexp_replace(E'--> text\\\0120815_12 <--', '[^[:alpha:]]', '', 'g'); regexp_replace text (1 row) Yours, Laurenz Albe ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] inner join problem with temporary tables
2007/6/14, guillermo arias <[EMAIL PROTECTED]>: could you please give me an example?. How could i make an inner join select with temporary tables? This function does not work: REATE OR REPLACE FUNCTION modelo.test2(OUT xart_cod character varying, OUT xart_descri character varying) RETURNS SETOF record AS $BODY$ begin create temp table t_arti as (select art_cod,art_descri from modelo.articulos); select $1,$2 from t_arti ; end; $BODY$ LANGUAGE 'plpgsql' VOLATILE; this is the error message: ERROR: query has no destination for result data SQL state: 42601 Hint: If you want to discard the results of a SELECT, use PERFORM instead. Context: PL/pgSQL function "test2" line 4 at SQL statement every select's output in plpgsql have to be redirected into variables. http://www.postgresql.org/docs/8.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW regards Pavel Stehule ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] strange change (and error) in 8.3 ?
hi, this query: select 1 where '1'::text in (1::int8); worked fine in 8.2: # select version(); version PostgreSQL 8.2.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2(Ubuntu 4.1.2-0ubuntu4) (1 row) # select 1 where '1'::text in (1::int8); ?column? -- 1 (1 row) but in 8.3 i get: # select 1 where '1'::text in (1::int8); ERROR: operator does not exist: text = bigint LINE 1: select 1 where '1'::text in (1::int8); ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. why? i mean - i see that types are bad, but it worked in 8.2. why it was broken/modified in 8.3? depesz -- http://www.depesz.com/ - nowy, lepszy depesz
Re: [GENERAL] PostGreSQL for a small Desktop Application
Gabriele wrote: I'm going to develop a medium sized business desktop client server application which will be deployed mostly on small sized networks and later eventually, hopefully, on medium sized networks. It will probably be developed using C#. I do need a solid DBMS wich can work with .Net framework. I do know PostGreSQL is a good DBMS in general (it sports most of the advanced DBMS features, transactions and stored procedure included) but i wonder if it is suited for my application. While PG has tons more features than SQLite, the major question here is: do you really need a database _server_? One thing that PG is designed for is handling many (as in 100) concurrent users. Database users, that is, meaning processes (running on different computers) opening a connection and issueing queries. Of course, it handles it very well also when those processes all run on a single server (and all connections are local connections), such as an HTTP server running, say, PHP. That model is very similar to the distributed one, since there's no state shared by the httpd/PHP processes. All shared state is inside the database server. It also happens to be persistant. Technically, that's not simply client/server, it's 3-tier, with httpd/PHP processes being multiple instances of a middle layer. As far the database server (PG) is concerned, those are (multiple) clients. In this scenario PostgreSQL is at home, being that what it's designed for. To tell the truth, *any* serious RDBMS out there would do. SQLite won't, tho, since it's not a server at all - it's just a library. But you mentioned using C#/.Net. AFAIK (but I'm no expert) that's yet a different model. You have a single process (although very likely multithreaded) which is able to hold a shared state while serving concurrent clients. Here, a database is "just" a backend for persistent state (that it, across reboots or crashes). Any good (thread-safe) library that writes to files would do. If you need/want SQL, SQLite comes into play. Actually, this is what it was designed for. It's much easier to install (it's all in a .dll) and administer (close to zero administration I think) than PostgreSQL (or any RDBMS). For such an use, PG would surely do, but may be just overkill. PG still has advantages vs. SQLite, being more featured (do you need stored-procedures?). But if you plan to use an ORM tool for .Net (see: http://www.google.com/search?q=ORM+.Net) you might even be able to switch between SQLite and PostgreSQL at any time w/o even noticing (be sure of choosing one that supports both backends, of course). I'm a big fan of both PG and SQLite, and happily use them. When I design an application, I ask myself: is this going to be a strongly database oriented app, with potentially different implementations of the middlelayer, or just a server that happens to need a solid and nice way to access data on disk? If you can answer to that, the choice is natural: use different tools for different purposes. But also remember that PG can functionally replace SQLite anywhere, but not the other way around. If you have room enough in your toolbox for just one tool, go PostgreSQL. I think the best thing about PG is that it's a terrific general purpose tool: a full RDBMS, extremely reliable, with no compromises, almost covering anything you might need in the features area (even more if you consider how easy is to extend it), yet light enough to be easily embeddable. .TM. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [SQL] function to find difference between in days between two dates
On Thu, Jun 14, 2007 at 09:00:12AM +0200, Pavel Stehule wrote: > Hello > > PostgreSQL hasn't any official function for it. If you need it, you > can write own function Not true. Anything that is done by an operator as actually done by a function. So the function exists: # \df date_mi List of functions Schema | Name | Result data type | Argument data types +-+--+- pg_catalog | date_mi | integer | date, date (1 row) You may have to dig through the pg_operator table to find it though. Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] pg_xlog - files are guaranteed to be sequentialynamed?
On Wed, 2007-06-13 at 21:39 +0200, Frank Wittig wrote: > But I would say one can > rely on serial file names to increase steadily. The whole of the PostgreSQL recovery system relies upon that, so yes we can use that externally from the database also. There's a patch to 8.3 for the restore_command to be passed a %r parameter so you don't need to grovel in the control file. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] precision of epoch
I'd like to convert timestamps without timezone to unix epoch values with at least microseconds resolution. but when i do e.g.: select extract (epoch from timestamp without time zone 'Thu 14 Jun 05:58:09.929994 2007'); i get: 1181793489.92999 so i loose the last digit. I'd expect 1181793489.929994 That's as well the behaviour, when I use columns of a real table. I seached the docs for a precsion value and even tried timestamp(6) as well but with no success. What am I missing here ? Gerhard ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [SQL] function to find difference between in days between two dates
2007/6/14, Martijn van Oosterhout <[EMAIL PROTECTED]>: On Thu, Jun 14, 2007 at 09:00:12AM +0200, Pavel Stehule wrote: > Hello > > PostgreSQL hasn't any official function for it. If you need it, you > can write own function Not true. Anything that is done by an operator as actually done by a function. So the function exists: # \df date_mi List of functions I know about it, but it's undocumented Pavel ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] pointer to feature comparisons, please
On 06/14/07 02:24, PFC wrote: The DELETE should block, no? Why ? Foreign keys put an ON DELETE trigger on the referenced table Foreign keys that silently, automatic DELETE records? Did I read that correctly? Isn't that the point of ON DELETE CASCADE ? Where'd that come from? Did I miss something in the thread? besides checking the referencing column on insert/update... If you just implement a constraint, you only get half the functionality. But when I define a FK *constraint*, that's all I *want*! When you add a REFERENCE foo( bar ) foreign key and don't specify ON DELETE clause, I believe you get ON DELETE NO ACTION, which means the referenced row can't be deleted if there are referencing rows... so when you have a FK constraint, it actually constrains two tables, and pg uses share locking to ensure race conditions can't cause an inconsistent database. -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] precision of epoch
On Thu, Jun 14, 2007 at 04:40:12AM -0700, [EMAIL PROTECTED] wrote: > I'd like to convert timestamps without timezone to unix epoch values > with at least microseconds resolution. > but when i do e.g.: > select extract (epoch from timestamp without time zone 'Thu 14 Jun > 05:58:09.929994 2007'); > > i get: > 1181793489.92999 > > so i loose the last digit. I'd expect 1181793489.929994 EXTRACT's return type is double precision, which isn't precise enough to represent that many significant digits. Notice that removing a digit from the beginning gives you another digit at the end: test=> SELECT '1181793489.929994'::double precision; float8 -- 1181793489.92999 (1 row) test=> SELECT '181793489.929994'::double precision; float8 -- 181793489.929994 (1 row) You could convert the epoch value to numeric but you'll have to use a more complex expression; simply casting EXTRACT's result to numeric won't work. One possibility might involve floor and to_char(value, '.US'). -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [SQL] function to find difference between in days between two dates
On Thu, Jun 14, 2007 at 01:54:09PM +0200, Pavel Stehule wrote: > ># \df date_mi > > I know about it, but it's undocumented Hrm, yet Bruce mentions it in one of his documents. http://momjian.us/main/writings/pgsql/data_processing.pdf It may be undocumented, but it get a lot of hits on google :) Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] [SQL] function to find difference between in days between two dates
Hrm, yet Bruce mentions it in one of his documents. http://momjian.us/main/writings/pgsql/data_processing.pdf It may be undocumented, but it get a lot of hits on google :) why google? look to pgsql/src/backend/utils/adt/date.c :-) Regards Pavel Stehule ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] precision of epoch
On Jun 14, 2:18 pm, [EMAIL PROTECTED] (Michael Fuhr) wrote: > On Thu, Jun 14, 2007 at 04:40:12AM -0700, [EMAIL PROTECTED] wrote: > > I'd like to convert timestamps without timezone to unix epoch values > > with at least microseconds resolution. > > but when i do e.g.: > > select extract (epoch from timestamp without time zone 'Thu 14 Jun > > 05:58:09.929994 2007'); > > > i get: > > 1181793489.92999 > > > so i loose the last digit. I'd expect 1181793489.929994 > > EXTRACT's return type is double precision, which isn't precise > enough to represent that many significant digits. Notice that > removing a digit from the beginning gives you another digit at > the end: > > test=> SELECT '1181793489.929994'::double precision; > float8 > -- > 1181793489.92999 > (1 row) > > test=> SELECT '181793489.929994'::double precision; > float8 > -- > 181793489.929994 > (1 row) > > You could convert the epoch value to numeric but you'll have to use > a more complex expression; simply casting EXTRACT's result to numeric > won't work. One possibility might involve floor and to_char(value, '.US'). > Your're righht, I did'nt take the 15 significant digit limitation of double into account, floor(extract(epoch from ts_column))||to_char(ts_column,'.US') does the job, but since the limitation is generally in double precision (in any language I process the result), I could as well use just extract(epoch). Thanks Gerhard ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] DeadLocks...
I found a problem with my application which only occurs under high loads
(isn't that always the case?).
snippets of perl...
insert into tokens (token)
select values.token
from (values TOKEN_LIST_STRING) as values(token)
left outer join tokens t using (token)
where t.token_idx is null
$sql =~ s/TOKEN_LIST_STRING/$string/
where $string is of the form (('one'),('two'))
This works 99% of the time.
But everyone once in a long while it seems that I hit simultaneaous
execute() statements that deadlock on the insertion.
Right now I know of no other way to handle this than to eval{ } the
execution and if it fails, sleep random milliseconds and retry... "wash
rinse repeat" for some number of times.
Is there any better way of doing this or some other means to manage
DEADLOCK?
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] DeadLocks..., DeadLocks...
<[EMAIL PROTECTED]> writes: > But everyone once in a long while it seems that I hit simultaneaous > execute() statements that deadlock on the insertion. What version of Postgres is this and do you have any foreign key constraints or triggers on the table you're inserting into? Is that insert the *only* DML you're executing? No updates or deletes? What do you mean by saying it deadlocks? Do you get a transaction abort with an error about a deadlock detected? Or do you just mean it freezes? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] pg_xlog - files are guaranteed to be sequentialy named?
Johannes Konert schrieb: > and time goes by, because everything seem to work just fine. One should never rely on something seeming to be any kind of anything. There are ways to _know_ things are right. Like using nagios to check every vital sign state of your systems. That should include checking system time against official time servers. But I also agree that if there is a possibility that times differ (and there of course is) the question is not if it can happen but when. Everything that can go wrong will go wrong - whatever you do to prevent things. Thats Finagle's law and its not proofed wrong yet. So your solution has to consider that. Therefore I disagree Greg in relying only on the system base. There have to be mechanisms which use reliable information that behave in only _one_ well known way (such as monotone increasing serial numbers) or the system will break. Regards, Frank Wittig signature.asc Description: OpenPGP digital signature
Re: [GENERAL] strange change (and error) in 8.3 ?
"hubert depesz lubaczewski" <[EMAIL PROTECTED]> writes: > but in 8.3 i get: > # select 1 where '1'::text in (1::int8); > ERROR: operator does not exist: text = bigint > why? i mean - i see that types are bad, but it worked in 8.2. why it was > broken/modified in 8.3? This is intentional --- implicit casts to text are gone. You should be happy that the above now fails, because it's calling your attention to the fact that you've got very ill-defined semantics there. Is the comparison going to be done according to text rules, or according to int8 rules? (This would matter, for instance, if there was a space in the string.) There are dozens of examples in the archives of people having been burnt by the old behavior, for instance http://archives.postgresql.org/pgsql-general/2007-02/msg01028.php http://archives.postgresql.org/pgsql-general/2007-02/msg00871.php http://archives.postgresql.org/pgsql-hackers/2005-11/msg00510.php regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] DeadLocks...
[EMAIL PROTECTED] wrote:
I found a problem with my application which only occurs under high loads
(isn't that always the case?).
snippets of perl...
insert into tokens (token)
select values.token
from (values TOKEN_LIST_STRING) as values(token)
left outer join tokens t using (token)
where t.token_idx is null
$sql =~ s/TOKEN_LIST_STRING/$string/
where $string is of the form (('one'),('two'))
This works 99% of the time.
But everyone once in a long while it seems that I hit simultaneaous
execute() statements that deadlock on the insertion.
Right now I know of no other way to handle this than to eval{ } the
execution and if it fails, sleep random milliseconds and retry... "wash
rinse repeat" for some number of times.
Is there any better way of doing this or some other means to manage
DEADLOCK?
Is this a deadlock that postgresql detects and causes one thread to roll
back and you can recover from, or are you talking about a deadlock that
isn't detected by postgresql and locks a thread?
What error messages are you seeing?
Generally speaking, if your operations have a potential for a deadlock,
the best you can do is to do what you're doing now, detect failure and
retry x times, then give up if it won't go through.
Or, redesign the way you're doing things.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] PostGreSQL for a small Desktop Application
On Thu, 14 Jun 2007, Marco Colombo wrote: PG still has advantages vs. SQLite, being more featured (do you need stored-procedures?). But if you plan to use an ORM tool for .Net you might even be able to switch between SQLite and PostgreSQL at any time w/o even noticing The main thing to be wary of when trying this is the SQLite deals with dates and times very differently than PostgreSQL does. Even when insulated with an ORM tool that can bite you. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] DeadLocks..., DeadLocks...
On 6/14/2007, "Gregory Stark" <[EMAIL PROTECTED]> wrote: > > ><[EMAIL PROTECTED]> writes: > >> But everyone once in a long while it seems that I hit simultaneaous >> execute() statements that deadlock on the insertion. > >What version of Postgres is this and do you have any foreign key constraints >or triggers on the table you're inserting into? Version 8.2 This table does not have foreign key constraints on it, but it is the source of foreign key constraints on other tables. No triggers. Is that insert the *only* DML >you're executing? No updates or deletes? At the time of the failure, no other DML. There are other's but they are on different tables. > >What do you mean by saying it deadlocks? Do you get a transaction abort with >an error about a deadlock detected? Or do you just mean it freezes? "deadlock detected" And the corresponding error I get is a primary key violation on the same table. The problem occurs when I have multiple processes acting on what appears to be the exact same set of information. I can't really control the issue of simultaneous/parallel processing ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] DeadLocks...
On 6/14/2007, "Scott Marlowe" <[EMAIL PROTECTED]> wrote:
>
>[EMAIL PROTECTED] wrote:
>> I found a problem with my application which only occurs under high loads
>> (isn't that always the case?).
>>
>> snippets of perl...
>>
>> insert into tokens (token)
>> select values.token
>> from (values TOKEN_LIST_STRING) as values(token)
>> left outer join tokens t using (token)
>> where t.token_idx is null
>>
>> $sql =~ s/TOKEN_LIST_STRING/$string/
>> where $string is of the form (('one'),('two'))
>>
>> This works 99% of the time.
>>
>> But everyone once in a long while it seems that I hit simultaneaous
>> execute() statements that deadlock on the insertion.
>>
>> Right now I know of no other way to handle this than to eval{ } the
>> execution and if it fails, sleep random milliseconds and retry... "wash
>> rinse repeat" for some number of times.
>>
>> Is there any better way of doing this or some other means to manage
>> DEADLOCK?
>Is this a deadlock that postgresql detects and causes one thread to roll
>back and you can recover from, or are you talking about a deadlock that
>isn't detected by postgresql and locks a thread?
>
>What error messages are you seeing?
>
>Generally speaking, if your operations have a potential for a deadlock,
>the best you can do is to do what you're doing now, detect failure and
>retry x times, then give up if it won't go through.
>
>Or, redesign the way you're doing things.
It's "deadlock detected" and rolling back.
I could prevent this if I only had one INSERT process running for all
that I'm doing, but that kind of negates the idea of having multiple
processes.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] pg_xlog - files are guaranteed to be sequentialy named?
On Thu, 14 Jun 2007, Frank Wittig wrote: But I also agree that if there is a possibility that times differ (and there of course is) the question is not if it can happen but when. Sure, but it doesn't matter one bit if the times between the primary and secondary servers differ. If the timestamps on the primary are being preserved when copied over, the secondary can operate on them with no reference whatsoever to its local time. Future transaction timestamps will be all screwed up when you switch to the secondary if its clock is wrong, but it doesn't impact the operation of the PITR mechanism or its cleanup. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] DeadLocks..., DeadLocks...
In response to <[EMAIL PROTECTED]>: > > On 6/14/2007, "Gregory Stark" <[EMAIL PROTECTED]> wrote: > > > > > > ><[EMAIL PROTECTED]> writes: > > > >> But everyone once in a long while it seems that I hit simultaneaous > >> execute() statements that deadlock on the insertion. > > > >What version of Postgres is this and do you have any foreign key constraints > >or triggers on the table you're inserting into? > > Version 8.2 > This table does not have foreign key constraints on it, but it is the > source of foreign key constraints on other tables. > No triggers. > > Is that insert the *only* DML > >you're executing? No updates or deletes? > > At the time of the failure, no other DML. > There are other's but they are on different tables. > > > >What do you mean by saying it deadlocks? Do you get a transaction abort with > >an error about a deadlock detected? Or do you just mean it freezes? > > "deadlock detected" > And the corresponding error I get is a primary key violation on the same > table. > > > The problem occurs when I have multiple processes acting on what appears > to be the exact same set of information. I can't really control the > issue of simultaneous/parallel processing Put an "ORDER BY" in your SELECT. I believe the problem is that when this runs from two different places, the DB may order the returned values in a different order for each one, which leads to the possibility of two similar inserts deadlocking. Unless I misunderstand your schema, you should be able to guarantee against deadlocking by guaranteeing that the SELECT portion will always return rows in the same order. -- Bill Moran http://www.potentialtech.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] explain analyze on a function
Is this possible? I've been searching posts, but to no luck ;( I have one SQL query inside function, but when i do select from function it takes 8 sec. If I execute just SQL query (with some parameters passed to it) it takes 0.3 seconds. What I'm trying to do is select part of the data from the table and then do some aggregation on it. Here is the function CREATE OR REPLACE FUNCTION raspored.dohvati_statistiku_rada_u_smjenama_radnika(IN do_datuma date, IN idodj integer, OUT radnik_id integer, OUT smjena_id integer, OUT ukupno_sati numeric, OUT ukupno_dana integer, OUT radnih_dana integer, OUT vikenda integer, OUT nedjelja integer, OUT praznika integer, OUT posto_radnih_dana numeric, OUT posto_vikenda numeric, OUT posto_nedjelja numeric, OUT posto_praznika numeric) RETURNS SETOF record AS $BODY$ select podaci.radnik_id, podaci.smjena_id, sum(podaci.broj_sati) as ukupno_sati, count(podaci.broj_dana)::int as ukupno_dana, count(radni_dani.broj_sati)::int as broj_radnih_dana, count(vikendi.broj_sati)::int as broj_vikenda, count(neradni_dani.broj_sati)::int as broj_neradnih_dana, count(praznici.broj_sati)::int as broj_praznika, count(radni_dani.broj_sati)/sum(podaci.broj_dana) as postotak_rd, count(vikendi.broj_sati)/sum(podaci.broj_dana) as postotak_vk, count(neradni_dani.broj_sati)/sum(podaci.broj_dana) as postotak_nrd, count(praznici.broj_sati)/sum(podaci.broj_dana) as postotak_prz from(select rr.datum, radnik_id, smjena_id, vrijeme.broj_sati(sum(trajanje_rada))::numeric as broj_sati, vrsta_dana_id, count(*) as broj_dana from raspored.raspored_rada rr, kalendar.dani_kalendara k, raspored.smjene where rr.datum<$1 and rr.datum>=$1-120 and rr.datum=k.datum and id_smjena=smjena_id and odjel_id=$2 group by 1,2,3,5) as podaci left join raspored.u_rasporedu_radni_dani_radnika radni_dani on podaci.radnik_id=radni_dani.radnik_id and podaci.smjena_id=radni_dani.smjena_id and podaci.datum=radni_dani.datum and podaci.vrsta_dana_id=radni_dani.vrsta_dana_id left join raspored.u_rasporedu_vikendi_radnika vikendi on podaci.radnik_id=vikendi.radnik_id and podaci.smjena_id=vikendi.smjena_id and podaci.datum=vikendi.datum and podaci.vrsta_dana_id=vikendi.vrsta_dana_id left join raspored.u_rasporedu_neradni_dani_radnika neradni_dani on podaci.radnik_id=neradni_dani.radnik_id and podaci.smjena_id=neradni_dani.smjena_id and podaci.datum=neradni_dani.datum and podaci.vrsta_dana_id=neradni_dani.vrsta_dana_id left join raspored.u_rasporedu_praznici_radnika praznici on podaci.radnik_id=praznici.radnik_id and podaci.smjena_id=praznici.smjena_id and podaci.datum=praznici.datum and podaci.vrsta_dana_id=praznici.vrsta_dana_id group by podaci.radnik_id, podaci.smjena_id $BODY$ LANGUAGE 'sql' STABLE SECURITY DEFINER; "Function Scan on dohvati_statistiku_rada_u_smjenama_radnika (cost=0.00..12.50 rows=1000 width=188) (actual time=8192.281..8192.714 rows=75 loops=1)" "Total runtime: 8192.888 ms" And here is the explain analyze from SQL with two parameters ($1=current_date and $2=4) "HashAggregate (cost=1023.94..1043.44 rows=200 width=112) (actual time=309.535..310.083 rows=75 loops=1)" " -> Hash Left Join (cost=975.44..1015.42 rows=213 width=112) (actual time=163.295..246.655 rows=1164 loops=1)" "Hash Cond: ((podaci.radnik_id = rr.radnik_id) AND (podaci.smjena_id = rr.smjena_id) AND (podaci.datum = rr.datum) AND (podaci.vrsta_dana_id = k.vrsta_dana_id))" "-> Hash Left Join (cost=773.34..804.79 rows=213 width=104) (actual time=135.081..213.059 rows=1164 loops=1)" " Hash Cond: ((podaci.radnik_id = rr.radnik_id) AND (podaci.smjena_id = rr.smjena_id) AND (podaci.datum = rr.datum) AND (podaci.vrsta_dana_id = k.vrsta_dana_id))" " -> Hash Left Join (cost=571.25..594.17 rows=213 width=88) (actual time=109.248..182.146 rows=1164 loops=1)" "Hash Cond: ((podaci.radnik_id = rr.radnik_id) AND (podaci.smjena_id = rr.smjena_id) AND (podaci.datum = rr.datum) AND (podaci.vrsta_dana_id = k.vrsta_dana_id))" "-> Hash Left Join (cost=369.15..383.54 rows=213 width=72) (actual time=64.537..129.266 rows=1164 loops=1)" " Hash Cond: ((podaci.radnik
Re: [GENERAL] changing the /tmp/ lock file?
On Thu, 14 Jun 2007, Robin Ericsson wrote: Why not running them on different ports and proxy the incoming ports via iptables or something like that based on the current situation on your backends? Because I figured it would be less hacky to have each postgres cluster listen on the address I want it to listen on, rather then listening on everything and then patching it up with iptables. But I see now that's not the case. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] changing the /tmp/ lock file?
On Thu, Jun 14, 2007 at 09:53:42AM -0700, Ben wrote: > Because I figured it would be less hacky to have each postgres cluster > listen on the address I want it to listen on, rather then listening on > everything and then patching it up with iptables. But I see now that's not > the case. It is possible, you just have to realise that just like every postmaster has to listen on a different IP, they also all need to listen to a different socket directory. Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] DeadLocks..., DeadLocks...
I'm still not precisely clear what's going on, it might help if you posted the actual schema and the deadlock message which lists the precise locks that deadlocked. Are any of the DML you mention on other tables on those tables with foreign key references to this one? It's impossible for two inserts on the same table to deadlock against each other so there must be more going on than what you've described. It's hard to help much without a complete picture. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] explain analyze on a function
Rikard Pavelic wrote: > Is this possible? > > I've been searching posts, but to no luck ;( > > I have one SQL query inside function, but when > i do select from function it takes 8 sec. > If I execute just SQL query (with some parameters passed to it) > it takes 0.3 seconds. Try doing a PREPARE and then EXPLAIN EXECUTE, like alvherre=# prepare foo as select generate_series(1, $1); PREPARE alvherre=# explain analyze execute foo(100); QUERY PLAN -- Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.014..0.294 rows=100 loops=1) Total runtime: 0.550 ms (2 filas) alvherre=# explain analyze execute foo(1); QUERY PLAN - Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.012..35.082 rows=1 loops=1) Total runtime: 59.077 ms (2 filas) -- Alvaro Herrera http://www.PlanetPostgreSQL.org/ "La fuerza no está en los medios físicos sino que reside en una voluntad indomable" (Gandhi) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] DeadLocks..., DeadLocks...
Gregory Stark wrote: > > I'm still not precisely clear what's going on, it might help if you posted the > actual schema and the deadlock message which lists the precise locks that > deadlocked. > > Are any of the DML you mention on other tables on those tables with foreign > key references to this one? Maybe this has to do with FKs and an old release, which used SELECT FOR UPDATE in the FK triggers. Those were well-known for causing deadlocks back then. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] explain analyze on a function
Alvaro Herrera wrote: > Try doing a PREPARE and then EXPLAIN EXECUTE, like > > alvherre=# prepare foo as select generate_series(1, $1); > PREPARE > > alvherre=# explain analyze execute foo(100); > QUERY PLAN > > -- > Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.014..0.294 rows=100 > loops=1) > Total runtime: 0.550 ms > (2 filas) > > alvherre=# explain analyze execute foo(1); >QUERY PLAN > > - > Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.012..35.082 > rows=1 loops=1) > Total runtime: 59.077 ms > (2 filas) > > > Hm, no luck ;( prepare f1 as select * from raspored.dohvati_statistiku_rada_u_smjenama_radnika($1, $2); explain analyze execute f1(current_date, 4); "Function Scan on dohvati_statistiku_rada_u_smjenama_radnika (cost=0.00..12.50 rows=1000 width=188) (actual time=8013.779..8013.906 rows=75 loops=1)" "Total runtime: 8014.073 ms" Regards, Rikard ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] strange change (and error) in 8.3 ?
On 6/14/07, Tom Lane <[EMAIL PROTECTED]> wrote: This is intentional --- implicit casts to text are gone. You should be happy that the above now fails, because it's calling your attention to the fact that you've got very ill-defined semantics there. Is the thanks for clarification. actually it's not my app, i'm just a bystander which was asked about the issue :). reasoning seems to be perfectly valid. i hope they will be happy with it as well :) best regards, depesz
Re: [GENERAL] explain analyze on a function
Rikard Pavelic wrote: > Alvaro Herrera wrote: > > Try doing a PREPARE and then EXPLAIN EXECUTE, like > > > > alvherre=# prepare foo as select generate_series(1, $1); > Hm, no luck ;( > > prepare f1 as select * from > raspored.dohvati_statistiku_rada_u_smjenama_radnika($1, $2); > explain analyze execute f1(current_date, 4); I meant the queries inside the function. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] explain analyze on a function
Alvaro Herrera wrote: > > I meant the queries inside the function. > > Oh ;( Here it is "HashAggregate (cost=825.10..825.19 rows=1 width=112) (actual time=59175.752..59176.301 rows=75 loops=1)" " -> Nested Loop Left Join (cost=443.57..825.06 rows=1 width=112) (actual time=148.338..58997.576 rows=1164 loops=1)" "Join Filter: ((podaci.radnik_id = rr.radnik_id) AND (podaci.smjena_id = rr.smjena_id) AND (podaci.datum = rr.datum) AND (podaci.vrsta_dana_id = k.vrsta_dana_id))" "-> Nested Loop Left Join (cost=336.84..622.96 rows=1 width=104) (actual time=124.497..46278.143 rows=1164 loops=1)" " Join Filter: ((podaci.radnik_id = rr.radnik_id) AND (podaci.smjena_id = rr.smjena_id) AND (podaci.datum = rr.datum) AND (podaci.vrsta_dana_id = k.vrsta_dana_id))" " -> Nested Loop Left Join (cost=230.11..420.87 rows=1 width=88) (actual time=100.447..34376.459 rows=1164 loops=1)" "Join Filter: ((podaci.radnik_id = rr.radnik_id) AND (podaci.smjena_id = rr.smjena_id) AND (podaci.datum = rr.datum) AND (podaci.vrsta_dana_id = k.vrsta_dana_id))" "-> Nested Loop Left Join (cost=123.38..218.77 rows=1 width=72) (actual time=57.764..13172.231 rows=1164 loops=1)" " Join Filter: ((podaci.radnik_id = rr.radnik_id) AND (podaci.smjena_id = rr.smjena_id) AND (podaci.datum = rr.datum) AND (podaci.vrsta_dana_id = k.vrsta_dana_id))" " -> HashAggregate (cost=16.65..16.67 rows=1 width=32) (actual time=31.240..117.905 rows=1164 loops=1)" "-> Nested Loop (cost=0.00..16.63 rows=1 width=32) (actual time=0.087..27.530 rows=1164 loops=1)" " -> Nested Loop (cost=0.00..9.58 rows=1 width=28) (actual time=0.072..8.849 rows=1164 loops=1)" "-> Seq Scan on smjene (cost=0.00..1.14 rows=1 width=4) (actual time=0.012..0.026 rows=3 loops=1)" " Filter: (odjel_id = $2)" "-> Index Scan using raspored_rada_pkey on raspored_rada rr (cost=0.00..8.43 rows=1 width=28) (actual time=0.039..1.639 rows=388 loops=3)" " Index Cond: ((rr.datum < $1) AND (rr.datum >= ($1 - 120)) AND (smjene.id_smjena = rr.smjena_id))" " -> Index Scan using dani_kalendara_pkey on dani_kalendara k (cost=0.00..7.04 rows=1 width=8) (actual time=0.004..0.006 rows=1 loops=1164)" "Index Cond: (rr.datum = k.datum)" " -> Hash Join (cost=106.73..191.50 rows=530 width=32) (actual time=0.036..10.679 rows=288 loops=1164)" "Hash Cond: (rr.datum = k.datum)" "-> Seq Scan on raspored_rada rr (cost=0.00..69.52 rows=2652 width=28) (actual time=0.008..5.424 rows=2620 loops=1164)" "-> Hash (cost=97.06..97.06 rows=774 width=8) (actual time=15.164..15.164 rows=508 loops=1)" " -> Hash Join (cost=1.08..97.06 rows=774 width=8) (actual time=9.112..14.167 rows=508 loops=1)" "Hash Cond: (k.vrsta_dana_id = postavke.vrste_dana.id_vrsta_dana)" "-> Seq Scan on dani_kalendara k (cost=0.00..73.72 rows=3872 width=8) (actual time=0.008..6.407 rows=3652 loops=1)" "-> Hash (cost=1.06..1.06 rows=1 width=4) (actual time=0.021..0.021 rows=1 loops=1)" " -> Seq Scan on vrste_dana (cost=0.00..1.06 rows=1 width=4) (actual time=0.008..0.013 rows=1 loops=1)" "Filter: ((naziv_vrste_dana)::text = 'vikend'::text)" "-> Hash Join (cost=106.73..191.50 rows=530 width=32) (actual time=0.034..14.539 rows=2070 loops=1164)" " Hash Cond: (rr.datum = k.datum)" " -> Seq Scan on raspored_rada rr (cost=0.00..69.52 rows=2652 width=28) (actual time=0.007..5.480 rows=2620 loops=1164)" " -> Hash (cost=97.06..97.06 rows=774 width=8) (actual time=23.487..23.487 rows=2528 loops=1)" "-> Hash Join (cost=1.08..97.06 rows=774 width=8) (actual time=0.054..18.583 rows=2528 loops=1)" " Hash Cond: (k.vrsta_dana_id = postavke.vrste_dana.id_vrsta_dana)" " -> Seq Scan on dani_kalendara k (cost=0.00..73.72 rows=3872 width=8) (actual time=0.008..6.779 rows=3652 loops=1)" " -> Hash (cost=1.06..1.06 rows=1 width=4) (actual time=0.024..0.024 rows=1 loops=1)" "-> Seq Scan on vrste_dana (cost=0.00..1.0
[GENERAL] COPY Command and a non superuser user?
How do I get a non superuser user to be able to run the COPY command? -- Thanks, Warren ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Function with COPY command?
Is there any way to make this function work? CREATE OR REPLACE FUNCTION import_text_file(char(255)) RETURNS void AS $$ DECLARE filename ALIAS FOR $1; BEGIN COPY table FROM filename; END; $$ LANGUAGE plpgsql; The version below works fine, but I need something like the above version. CREATE OR REPLACE FUNCTION import_text_file(char(255)) RETURNS void AS $$ DECLARE filename ALIAS FOR $1; BEGIN COPY table FROM 'C:\\somefile.txt'; END; $$ LANGUAGE plpgsql; -- Thanks, Warren ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] UNION ALL with the same ORDER BY on the parts and the result
Hi, I've got a table that stores private messages (like e-mails). Every row has a source_user, a destination_user, a timestamp, and information on whether the source and/or the destination user has already deleted the message. > CREATE TABLE privs ( > id serial NOT NULL, > src_usrid integer NOT NULL, > src_del boolean NOT NULL, > dst_usrid integer NOT NULL, > dst_del boolean NOT NULL, > timest timestamp with time zone NOT NULL, > content text NOT NULL, > CONSTRAINT privs_chk_noself CHECK ((src_usrid <> dst_usrid)) > ); There are two indices: > srcusrid_timest: (src_usrid, timest) WHERE (src_del IS FALSE) > dstusrid_timest: (dst_usrid, timest) WHERE (dst_del IS FALSE) The query I would like to optimize: > SELECT * FROM (( > SELECT * FROM privs > WHERE src_usrid = 1 AND src_del IS FALSE > ORDER BY timest DESC > ) UNION ALL ( > SELECT * FROM privs > WHERE dst_usrid = 1 AND dst_del IS FALSE > ORDER BY timest DESC > )) AS data > ORDER BY timest DESC I think the UNION ALL could be done like a "merge join", ie. scanning both subqueries simultaneously using the indices, and always adding the row with the greather timestamp to the result. But it appends the resultsets, and then does a sort. When I tried to do this with one query like: > WHERE (src_usrid = 1 AND src_del IS FALSE) >OR (dst_usrid = 1 AND dst_del IS FALSE) > ORDER BY timest DESC it chose to do a bitmap-or and then a sort. I'd like to avoid that sort, because it won't scale up very good as the table grows... is there a way I can do that? I can only think of self- made a function doing exactly the same that i wrote above... Regards, Denes Daniel 35% kedvezmény az Osiris Kiadó köteteire. TÉRjen be: egész héten várjuk programjainkkal az Alexandra Könyvtéren, a pécsi Széchenyi téren. http://ad.adverticum.net/b/cl,1,6022,176377,235993/click.prm ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Function with COPY command?
Warren <[EMAIL PROTECTED]> writes: > Is there any way to make this function work? > CREATE OR REPLACE FUNCTION import_text_file(char(255)) RETURNS void AS $$ > DECLARE > filename ALIAS FOR $1; > BEGIN > COPY table FROM filename; > END; > $$ LANGUAGE plpgsql; Use EXECUTE. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] COPY Command and a non superuser user?
Warren wrote: How do I get a non superuser user to be able to run the COPY command? You can copy to / from the stdin. non-superusers cannot run copy to / from a file, since the copy to / from a file does so with the access authority of the postgres user and could be used to do "bad things" TM ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Function with COPY command?
Something like this will help: CREATE OR REPLACE FUNCTION import_text_file(char(255)) RETURNS void AS $$ DECLARE filename ALIAS FOR $1; fin varchar; BEGIN fin := 'COPY table from ' || filename; execute fin; END; $$ LANGUAGE plpgsql; -- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 6/14/07, Tom Lane <[EMAIL PROTECTED]> wrote: Warren <[EMAIL PROTECTED]> writes: > Is there any way to make this function work? > CREATE OR REPLACE FUNCTION import_text_file(char(255)) RETURNS void AS $$ > DECLARE > filename ALIAS FOR $1; > BEGIN > COPY table FROM filename; > END; > $$ LANGUAGE plpgsql; Use EXECUTE. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Hash Aggregate plan picked for very large table == out of memory
With Postgresql 8.1.9 -- I have a simple group by query: SELECT target_page_id, min(created_at) FROM page_page_link GROUP BY 1; The page_page_link table has ~130 million rows. After analyzing the table, the planner picks a hash aggregate plan, which results in an out of memory error. crystal=> analyze page_page_link; ANALYZE crystal=> explain crystal-> SELECT target_page_id as page_id, min(created_at) as created_at crystal-> FROM page_page_link crystal-> GROUP By 1 crystal-> ; QUERY PLAN --- HashAggregate (cost=3663517.88..3670393.09 rows=550017 width=12) -> Seq Scan on page_page_link (cost=0.00..2993649.92 rows=133973592 width=12) (2 rows) The default_statistics_target was originally 200. I upped it to 1000 and still get the same results. crystal=> show default_statistics_target; default_statistics_target --- 1000 (1 row) crystal=> set enable_hashagg = off; SET crystal=> explain crystal-> SELECT target_page_id as page_id, min(created_at) as created_at crystal-> FROM page_page_link crystal-> GROUP BY 1 crystal-> ; QUERY PLAN - GroupAggregate (cost=27240841.37..28252518.53 rows=550017 width=12) -> Sort (cost=27240841.37..27575775.35 rows=133973592 width=12) Sort Key: target_page_id -> Seq Scan on page_page_link (cost=0.00..2993649.92rows=133973592 width=12) (4 rows) crystal=> I am working around this by setting enable_hashagg = off -- but it just seems like a case where the planner is not picking the strategy? Is there another setting I can change to help make better decisions? thanks in advance, Mason
[GENERAL] pg_restore out of memory
I am trying to restore a file that was done with pg_dump -Fc pg_dump on a postgreql 8.1.4 machine pg_restore on a postgresql 8.2.4 machine. The restore machine has the following settings. /etc/sysctl.conf (FreeBSD machine) kern.ipc.shmall=262144 kern.ipc.shmmax=534773760 #510MB kern.ipc.semmap=256 /boot/loader.conf kern.ipc.semmni=256 kern.ipc.semmns=512 kern.ipc.semmnu=256 kern.maxdsiz="1600MB" #1.6GB kern.dfldsiz="1600MB" #1.6GB kern.maxssiz="128M" # 128MB shared_buffers = 450MB temp_buffers = 8MB work_mem = 8MB # min 64kB maintenance_work_mem = 64M max_fsm_pages = 500 #Had error with 100,000 and increased to 5Million while trying the pg_restore OS can see 3.5GB of RAM. Swap is 20GB. The error that I got was: pg_restore: [archiver (db)] Error from TOC entry 2146; 0 988154 TABLE DATA message_attachments pgsql pg_restore: [archiver (db)] COPY failed: ERROR: out of memory DETAIL: Failed on request of size 134217728 (128MB) Syslog was: Jun 14 10:17:56 bk20 postgres[7294]: [130-1] ERROR: out of memory Jun 14 10:17:56 bk20 postgres[7294]: [130-2] DETAIL: Failed on request of size 134217728. I see a simmilar thread back in December 2006. http://tinyurl.com/3aa29g However i don't see a final resolution. Which parameter do I need to increase? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] pg_restore out of memory
Francisco Reyes <[EMAIL PROTECTED]> writes: > Syslog was: > Jun 14 10:17:56 bk20 postgres[7294]: [130-1] ERROR: out of memory > Jun 14 10:17:56 bk20 postgres[7294]: [130-2] DETAIL: Failed on request of > size 134217728. Can we see the context-sizes dump that should've come out right before that in the log? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Historical Data Question
On Jun 14, 12:57 pm, Lza <[EMAIL PROTECTED]> wrote: > Can anyone help me with this problem? > > I have a table in my database that holds information on policies and > this table is linked to a number of other tables. I need to be able to > keep a history of all changes to a policy over time. The other tables > that are linked to policy also need to store historical data. When I > run a query on the policy table for a certain period, I also need to > be able to pull the correct related rows (i.e. the information that > would have been in the table at that time) from the tables linked to > it. > > Does anyone have any suggestions on how to store historical > information in databases? Any good resources (books, etc..) that cover > this information? > > Thanks for your time. See: http://www.postgresql.org/docs/8.2/static/plpgsql-trigger.html Search for: "Example 37-3. A PL/pgSQL Trigger Procedure For Auditing" ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Hash Aggregate plan picked for very large table == out of memory
"Mason Hale" <[EMAIL PROTECTED]> writes: > SELECT target_page_id, min(created_at) > FROM page_page_link > GROUP BY 1; > The page_page_link table has ~130 million rows. > After analyzing the table, the planner picks a hash aggregate plan, which > results in an out of memory error. What have you got work_mem set to? What's the actual number of groups (target_page_id values)? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Hash Aggregate plan picked for very large table == out of memory
Thanks Tom. Here's more info: What have you got work_mem set to? 40960 What's the actual number of groups (target_page_id values)? Approximately 40 million (I'll have a more precise number when the query finishes running ). Maybe this helps? crystal=> select null_frac, n_distinct, correlation from pg_stats where tablename = 'page_page_link' and attname = 'target_page_id'; null_frac | n_distinct | correlation ---++- 0 | 550017 |0.240603 (1 row) Mason
Re: [GENERAL] Hash Aggregate plan picked for very large table == out of memory
I should have mentioned this previously: running the same query against the same data on 8.1.5 does not result in a hash aggregate plan or an out of memory error. (Note: the hardware is different but very similar -- the main difference is the 8.1.9 server (with the error) has faster disks) On 6/14/07, Mason Hale <[EMAIL PROTECTED]> wrote: Thanks Tom. Here's more info: What have you got work_mem set to? 40960 What's the actual number of groups > (target_page_id values)? Approximately 40 million (I'll have a more precise number when the query finishes running ). Maybe this helps? crystal=> select null_frac, n_distinct, correlation from pg_stats where tablename = 'page_page_link' and attname = 'target_page_id'; null_frac | n_distinct | correlation ---++- 0 | 550017 |0.240603 (1 row) Mason
Re: [GENERAL] Hash Aggregate plan picked for very large table == out of memory
"Mason Hale" <[EMAIL PROTECTED]> writes: >> What's the actual number of groups >> (target_page_id values)? > Approximately 40 million (I'll have a more precise number when the query > finishes running ). Ouch. The discrepancy between that and the 550K estimate is what's killing you --- the hash table will be 80 times bigger than the planner thinks. You sure that increasing the stats target doesn't improve matters? (I suppose the distribution is one of these "long tail" affairs...) The best solution might be to reduce work_mem --- 40MB is fairly high for a general-purpose setting anyway IMO, since you need to worry about complex queries eating multiples of work_mem. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] pg_restore out of memory
Tom Lane writes: Francisco Reyes <[EMAIL PROTECTED]> writes: Syslog was: Jun 14 10:17:56 bk20 postgres[7294]: [130-1] ERROR: out of memory Jun 14 10:17:56 bk20 postgres[7294]: [130-2] DETAIL: Failed on request of size 134217728. Can we see the context-sizes dump that should've come out right before that in the log? Is this the one you need? Is was right after the error Jun 14 10:17:56 bk20 postgres[7294]: [130-3] CONTEXT: COPY message_attachments, line 60490: "2720290 7225017 research/crew holds.sit sit 5753t 1 Jun 14 10:17:56 bk20 postgres[7294]: [130-4] U3R1ZmZJdCAoYykxOTk3LTIwMDIgQWxhZGRpbiBTeX..." Jun 14 10:17:56 bk20 postgres[7294]: [130-5] STATEMENT: COPY message_attachments (attachment_id, message_id, filename, extension, attachment_size, name_real, Jun 14 10:17:56 bk20 postgres[7294]: [130-6] parser_version, attachment_search_text, attachment_body, delete_status, delete_status_date) FROM stdin; ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Hash Aggregate plan picked for very large table == out of memory
"Mason Hale" <[EMAIL PROTECTED]> writes: > The default_statistics_target was originally 200. > I upped it to 1000 and still get the same results. You did analyze the table after upping the target right? Actually I would expect you would be better off not raising it so high globally and just raising it for this one table with ALTER [ COLUMN ] column SET STATISTICS integer > I am working around this by setting enable_hashagg = off -- but it just > seems like a case where the planner is not picking the strategy? Sadly guessing the number of distinct values from a sample is actually a pretty hard problem. How many distinct values do you get when you run with enable_hashagg off? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] pg_restore out of memory
"Francisco Reyes" <[EMAIL PROTECTED]> writes: > kern.maxdsiz="1600MB" #1.6GB > kern.dfldsiz="1600MB" #1.6GB > kern.maxssiz="128M" # 128MB It ought to be maxdsiz which seems large enough. > The error that I got was: > pg_restore: [archiver (db)] Error from TOC entry 2146; 0 988154 TABLE DATA > message_attachments pgsql > pg_restore: [archiver (db)] COPY failed: ERROR: out of memory > DETAIL: Failed on request of size 134217728 (128MB) What does the output of "ulimit -a" show? Can you arrange to run ulimit -a in the same environment as the server? Either by starting the server in shell manually or by putting ulimit -a in the startup script which starts the server if you have one? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] DeadLocks..., DeadLocks...
Gregory Stark wrote: I'm still not precisely clear what's going on, it might help if you posted the actual schema and the deadlock message which lists the precise locks that deadlocked. Are any of the DML you mention on other tables on those tables with foreign key references to this one? It's impossible for two inserts on the same table to deadlock against each other so there must be more going on than what you've described. It's hard to help much without a complete picture. This is an example of what comes out of the apache logs... [Thu Jun 14 19:29:41 2007] [warn] mod_fcgid: stderr: DBD::Pg::db do failed: ERROR: deadlock detected [Thu Jun 14 19:29:41 2007] [warn] mod_fcgid: stderr: DETAIL: Process 16214 waits for ShareLock on transaction 297563; blocked by process 16211. This is what I found in my postgresql logs (after I turned on a few more items). I can repeat this really easily. Is there specific flags I should enable/disable for logging for this? My guess is the problem is related to 'insert into history_token..." but I haven't any Process ID's in here to be certain. 2007-06-14 19:50:35 EDT LOG: execute dbdpg_11: insert into history(signature) values ($1) 2007-06-14 19:50:35 EDT DETAIL: parameters: $1 = '53111e6c5c65570ec2e85636271a5b90' 2007-06-14 19:50:35 EDT LOG: duration: 0.169 ms 2007-06-14 19:50:35 EDT LOG: statement: select history_idx from history where signature = '53111e6c5c65570ec2e85636271a5b90' 2007-06-14 19:50:35 EDT LOG: duration: 0.328 ms 2007-06-14 19:50:35 EDT LOG: statement: insert into history_token(history_idx, token_idx) select values.history_idx, values.token_idx from ( values (2703,260),(2703,31789),(2703,1518),(2703,59),(2703,555),(2703,4),(2703,66447),(2703,8178),(2703,64),(2703,132),(2703,6126),(2703,135),(2 703,69),(2703,9166),(2703,629),(2703,73),(2703,74),(2703,2271),(2703,78),(2703,493),(2703,8164),(2703,211),(2703,8166),(2703,84),(2703,60608),(2703,217),(2703, 88),(2703,8207),(2703,161),(2703,33518),(2703,220),(2703,222),(2703,446),(2703,2188),(2703,336),(2703,1197),(2703,166),(2703,1537),(2703,28),(2703,168),(2703,2 481),(2703,1081),(2703,99),(2703,100),(2703,172),(2703,8209),(2703,231),(2703,1900),(2703,344),(2703,104),(2703,24694),(2703,106),(2703,37),(2703,107),(2703,17 9),(2703,8203),(2703,85629),(2703,3671),(2703,98970),(2703,8187),(2703,187),(2703,306),(2703,254),(2703,415),(2703,256),(2703,257),(2703,98975),(2703,98976),(2 703,98977),(2703,98978) ) as values(history_idx, token_idx) left outer join history_token ht using (history_idx, token_idx) where ht.history_idx is null 2007-06-14 19:50:35 EDT ERROR: deadlock detected 2007-06-14 19:50:35 EDT DETAIL: Process 17253 waits for ShareLock on transaction 303949; blocked by process 17229. Process 17229 waits for ShareLock on transaction 303950; blocked by process 17253. 2007-06-14 19:50:35 EDT STATEMENT: update tokens set last_seen = now() where token_idx in (260,31789,1518,59,555,4,66447,8178,64,132,6126,135,69,9166,629,73,7 4,2271,78,493,8164,211,8166,84,60608,217,88,8207,161,33518,220,222,446,2188,336,1197,166,1537,28,168,2481,1081,99,100,172,98963,8209,231,1900,344,104,24694,106 ,37,107,179,8203,85629,3671,8187,187,306,254,415,256,257,98968,98969,98970,98971) 2007-06-14 19:50:35 EDT LOG: disconnection: session time: 0:00:13.810 user=spam database=spam host=127.0.0.1 port=38126 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] pg_restore out of memory
Gregory Stark writes: What does the output of "ulimit -a" show? In FreeBSD, as far as I know, what controls the size of a program is the /boot/loader.conf and /etc/login.conf The default /etc/login.conf has unlimited size. /boot/loader.conf is set to max program size of 1.6GB Can you arrange to run ulimit -a in the same environment as the server? There is no "ulimit -a" in cshell which is what I use. I guessed this may be a bash setting .. so tried that.. The output of ulimit -a is: core file size (blocks, -c) unlimited data seg size (kbytes, -d) 524288 file size (blocks, -f) unlimited max locked memory (kbytes, -l) unlimited max memory size (kbytes, -m) unlimited open files (-n) 11095 pipe size(512 bytes, -p) 1 stack size (kbytes, -s) 65536 cpu time (seconds, -t) unlimited max user processes (-u) 5547 virtual memory (kbytes, -v) unlimited Don't see any limit at 128MB, the size at which the program crashed. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] DeadLocks..., DeadLocks...
Gregory Stark wrote:
I'm still not precisely clear what's going on, it might help if you posted the
actual schema and the deadlock message which lists the precise locks that
deadlocked.
Are any of the DML you mention on other tables on those tables with foreign
key references to this one?
It's impossible for two inserts on the same table to deadlock against each
other so there must be more going on than what you've described. It's hard to
help much without a complete picture.
I think I found the problem. And it's not at all where I thought it was.
Process 17583 waits for ShareLock on transaction 306841;
blocked by process 17725.
Process 17725 waits for ShareLock on transaction 306840;
blocked by process 17583.
Where I'm at a lost is the deadlocks reported are on different tables.
However, getting back to the Foreign Key question
history_token does have a foreign key constraint on tokens.token_idx on delete
cascade.
So is the INSERT statement on history_token getting deadlocked by the token
UPDATE statement? Looks that way and the only think I can see causing that
might be a foreign key issue.
Am I correctly identifying the problem?
Any options?
2007-06-14 19:58:43 EDT 17725 306927 LOG: statement: select token_idx from
tokens where token in ('ShareLock','hdr:414A79FBC82','ht.history_idx','2271','hdr:
2007-06-14 19:58:31 EDT 17583 306840 LOG: statement: insert into
history_token(history_idx, token_idx)
select values.history_idx, values.token_idx
from ( values
(2862,260),(2862,31789),(2862,1518),(2862,59),(2862,555),(2862,4),(2862,66447),(2862,8178),(2862,64),(2862,132),(2862,6126),(2862,135),(2
862,69),(2862,9166),(2862,629),(2862,73),(2862,74),(2862,2271),(2862,78),(2862,493),(2862,8164),(2862,211),(2862,8166),(2862,84),(2862,60608),(2862,217),(2862,
88),(2862,8207),(2862,161),(2862,33518),(2862,220),(2862,222),(2862,446),(2862,2188),(2862,336),(2862,1197),(2862,166),(2862,1537),(2862,28),(2862,168),(2862,2
481),(2862,1081),(2862,99),(2862,100),(2862,172),(2862,8209),(2862,231),(2862,1900),(2862,344),(2862,104),(2862,24694),(2862,106),(2862,37),(2862,107),(2862,17
9),(2862,8203),(2862,99140),(2862,85629),(2862,3671),(2862,8187),(2862,187),(2862,306),(2862,254),(2862,415),(2862,256),(2862,257),(2862,99227),(2862,99228),(2
862,99229),(2862,99230) ) as values(history_idx, token_idx)
left outer join history_token ht using (history_idx, token_idx)
where ht.history_idx is null
2007-06-14 19:58:31 EDT 17725 306841 LOG: statement: update tokens set
last_seen = now() where token_idx in
(260,31789,1518,59,555,4,66447,8178,64,132,6126,13
5,69,9166,629,73,74,2271,78,493,8164,211,8166,84,99222,60608,217,88,8207,161,33518,220,222,446,2188,336,1197,166,1537,28,168,2481,1081,99,100,172,8209,231,1900
,344,104,24694,106,37,107,179,8203,99140,85629,3671,8187,187,306,254,415,256,257,99224,99225,99226)
2007-06-14 19:58:31 EDT 17657 306842 LOG: duration: 0.033 ms
2007-06-14 19:58:31 EDT 17657 306842 LOG: execute dbdpg_105: insert into
user_history(user_idx, history_idx, seen_as) values ($1,$2,'noscore')
2007-06-14 19:58:31 EDT 17657 306842 DETAIL: parameters: $1 = '1', $2 = '2853'
2007-06-14 19:58:31 EDT 17657 306842 LOG: duration: 0.194 ms
2007-06-14 19:58:32 EDT 17657 306843 LOG: statement: DEALLOCATE dbdpg_105
2007-06-14 19:58:32 EDT 17657 0 LOG: duration: 0.164 ms
2007-06-14 19:58:32 EDT 17657 306844 LOG: statement: select h_msgs, s_msgs from
user_token where user_idx = 1 and token_idx in (260,31789,1518,59,555,4,66447,
8178,64,132,6126,135,69,9166,629,73,74,2271,78,493,8164,211,8166,84,60608,217,88,8207,161,33518,220,222,446,2188,336,1197,166,1537,28,168,2481,1081,99,100,172,
8209,231,1900,344,104,24694,106,37,107,179,8203,99140,85629,3671,8187,187,306,254,415,256,257,99216,99217,99218,99219)
2007-06-14 19:58:32 EDT 17657 0 LOG: duration: 1.408 ms
2007-06-14 19:58:32 EDT 17657 306845 LOG: statement: update tokens set
last_seen = now() where token_idx in
(260,31789,1518,59,555,4,66447,8178,64,132,6126,13
5,69,9166,629,73,74,2271,78,493,8164,211,8166,84,60608,217,88,8207,161,33518,220,222,446,2188,336,1197,166,1537,28,168,2481,1081,99,100,172,8209,231,1900,344,1
04,24694,106,37,107,179,8203,99140,85629,3671,8187,187,306,254,415,256,257,99216,99217,99218,99219)
2007-06-14 19:58:33 EDT 17583 306840 ERROR: deadlock detected
2007-06-14 19:58:33 EDT 17583 306840 DETAIL: Process 17583 waits for ShareLock
on transaction 306841; blocked by process 17725.
Process 17725 waits for ShareLock on transaction 306840; blocked by
process 17583.
2007-06-14 19:58:33 EDT 17583 306840 CONTEXT: SQL statement "SELECT 1 FROM ONLY
"public"."tokens" x WHERE "token_idx" = $1 FOR SHARE OF x"
2007-06-14 19:58:33 EDT 17583 306840 STATEMENT: insert into
history_token(history_idx, token_idx)
select values.history_idx, values.token_idx
from ( values
(2862,260),(2862,31789),(2862,1518),(2862,59),(2862,555),(2862,4),(2862,66447),(2862,8178),(2862,6
Re: [GENERAL] High-availability
Although I rarely see it mentioned, Skype has some replication tools that they opensourced. https://developer.skype.com/SkypeGarage/DbProjects/SkyTools ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] DeadLocks..., DeadLocks...
Tom Allison wrote:
Gregory Stark wrote:
I'm still not precisely clear what's going on, it might help if you
posted the
actual schema and the deadlock message which lists the precise locks that
deadlocked.
Are any of the DML you mention on other tables on those tables with
foreign
key references to this one?
It's impossible for two inserts on the same table to deadlock against
each
other so there must be more going on than what you've described. It's
hard to
help much without a complete picture.
I think I found the problem. And it's not at all where I thought it was.
Process 17583 waits for ShareLock on transaction 306841;
blocked by process 17725.
Process 17725 waits for ShareLock on transaction 306840;
blocked by process 17583.
Where I'm at a lost is the deadlocks reported are on different tables.
However, getting back to the Foreign Key question
history_token does have a foreign key constraint on tokens.token_idx on
delete cascade.
So is the INSERT statement on history_token getting deadlocked by the
token UPDATE statement? Looks that way and the only think I can see
causing that might be a foreign key issue.
Am I correctly identifying the problem?
Any options?
HISTORY_TOKEN:
{
eval{$dbh->do($sql)};
if ($@) {
if ($@ =~ /deadlock detected/) {
warn "$$: deadlock detected on HISTORY_TOKEN\n";
usleep 150_000;
warn "$$: retrying HISTORY_TOKEN\n";
redo HISTORY_TOKEN;
}
croak "$sql\n$dbh->[EMAIL PROTECTED]";
}
};
This seems to help a lot.
At least it's getting done.
Now, is there a shorter usleep time I can use safely or should I just leave well
enough alone?
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] DeadLocks..., DeadLocks...
Terry Fielder wrote: My 2 cents: I used to get a lot of these sharelock problems. Users using different records, but same tables in different order. (apparently 7.x was not as good as 8.x at row level locking) I was advised to upgrade from 7.x to 8.x I did, and all those sharelock problems went away. I'm on version 8.2 and not all the problems have gone away. All I can do right now is just trap the error and retry... Gets bogged down after a while. Not sure how much of a limitation the hardware is but 6 users and I start to run into a deadlock almost every 10 seconds. I rarely need to go to 6 users, but it's interesting to see what happens when I do. I'm finding the length of time necessary to wait for a retry can very a lot. But I'm open to suggestions. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] DeadLocks..., DeadLocks...
My 2 cents:
I used to get a lot of these sharelock problems.
Users using different records, but same tables in different order.
(apparently 7.x was not as good as 8.x at row level locking)
I was advised to upgrade from 7.x to 8.x
I did, and all those sharelock problems went away.
Terry
Terry Fielder
[EMAIL PROTECTED]
Associate Director Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
Fax: (416) 441-9085
Tom Allison wrote:
Gregory Stark wrote:
I'm still not precisely clear what's going on, it might help if you
posted the
actual schema and the deadlock message which lists the precise locks
that
deadlocked.
Are any of the DML you mention on other tables on those tables with
foreign
key references to this one?
It's impossible for two inserts on the same table to deadlock against
each
other so there must be more going on than what you've described. It's
hard to
help much without a complete picture.
I think I found the problem. And it's not at all where I thought it was.
Process 17583 waits for ShareLock on transaction 306841;
blocked by process 17725.
Process 17725 waits for ShareLock on transaction 306840;
blocked by process 17583.
Where I'm at a lost is the deadlocks reported are on different tables.
However, getting back to the Foreign Key question
history_token does have a foreign key constraint on tokens.token_idx
on delete cascade.
So is the INSERT statement on history_token getting deadlocked by the
token UPDATE statement? Looks that way and the only think I can see
causing that might be a foreign key issue.
Am I correctly identifying the problem?
Any options?
2007-06-14 19:58:43 EDT 17725 306927 LOG: statement: select token_idx
from tokens where token in
('ShareLock','hdr:414A79FBC82','ht.history_idx','2271','hdr:
2007-06-14 19:58:31 EDT 17583 306840 LOG: statement: insert into
history_token(history_idx, token_idx)
select values.history_idx, values.token_idx
from ( values
(2862,260),(2862,31789),(2862,1518),(2862,59),(2862,555),(2862,4),(2862,66447),(2862,8178),(2862,64),(2862,132),(2862,6126),(2862,135),(2
862,69),(2862,9166),(2862,629),(2862,73),(2862,74),(2862,2271),(2862,78),(2862,493),(2862,8164),(2862,211),(2862,8166),(2862,84),(2862,60608),(2862,217),(2862,
88),(2862,8207),(2862,161),(2862,33518),(2862,220),(2862,222),(2862,446),(2862,2188),(2862,336),(2862,1197),(2862,166),(2862,1537),(2862,28),(2862,168),(2862,2
481),(2862,1081),(2862,99),(2862,100),(2862,172),(2862,8209),(2862,231),(2862,1900),(2862,344),(2862,104),(2862,24694),(2862,106),(2862,37),(2862,107),(2862,17
9),(2862,8203),(2862,99140),(2862,85629),(2862,3671),(2862,8187),(2862,187),(2862,306),(2862,254),(2862,415),(2862,256),(2862,257),(2862,99227),(2862,99228),(2
862,99229),(2862,99230) ) as values(history_idx, token_idx)
left outer join history_token ht using (history_idx, token_idx)
where ht.history_idx is null
2007-06-14 19:58:31 EDT 17725 306841 LOG: statement: update tokens
set last_seen = now() where token_idx in
(260,31789,1518,59,555,4,66447,8178,64,132,6126,13
5,69,9166,629,73,74,2271,78,493,8164,211,8166,84,99222,60608,217,88,8207,161,33518,220,222,446,2188,336,1197,166,1537,28,168,2481,1081,99,100,172,8209,231,1900
,344,104,24694,106,37,107,179,8203,99140,85629,3671,8187,187,306,254,415,256,257,99224,99225,99226)
2007-06-14 19:58:31 EDT 17657 306842 LOG: duration: 0.033 ms
2007-06-14 19:58:31 EDT 17657 306842 LOG: execute dbdpg_105: insert
into user_history(user_idx, history_idx, seen_as) values
($1,$2,'noscore')
2007-06-14 19:58:31 EDT 17657 306842 DETAIL: parameters: $1 = '1', $2
= '2853'
2007-06-14 19:58:31 EDT 17657 306842 LOG: duration: 0.194 ms
2007-06-14 19:58:32 EDT 17657 306843 LOG: statement: DEALLOCATE
dbdpg_105
2007-06-14 19:58:32 EDT 17657 0 LOG: duration: 0.164 ms
2007-06-14 19:58:32 EDT 17657 306844 LOG: statement: select h_msgs,
s_msgs from user_token where user_idx = 1 and token_idx in
(260,31789,1518,59,555,4,66447,
8178,64,132,6126,135,69,9166,629,73,74,2271,78,493,8164,211,8166,84,60608,217,88,8207,161,33518,220,222,446,2188,336,1197,166,1537,28,168,2481,1081,99,100,172,
8209,231,1900,344,104,24694,106,37,107,179,8203,99140,85629,3671,8187,187,306,254,415,256,257,99216,99217,99218,99219)
2007-06-14 19:58:32 EDT 17657 0 LOG: duration: 1.408 ms
2007-06-14 19:58:32 EDT 17657 306845 LOG: statement: update tokens
set last_seen = now() where token_idx in
(260,31789,1518,59,555,4,66447,8178,64,132,6126,13
5,69,9166,629,73,74,2271,78,493,8164,211,8166,84,60608,217,88,8207,161,33518,220,222,446,2188,336,1197,166,1537,28,168,2481,1081,99,100,172,8209,231,1900,344,1
04,24694,106,37,107,179,8203,99140,85629,3671,8187,187,306,254,415,256,257,99216,99217,99218,99219)
2007-06-14 19:58:33 EDT 17583 306840 ERROR: deadlock detected
2007-06-14 19:58:33 EDT 17583 306840 DETAIL: Process 17583 waits for
ShareLock on transaction 306841; blocked by process 17725.
Proc
Re: [GENERAL] [SQL] setof or array as input parameter to postgresql 8.2 functions
visit http://www.postgresql.org/community/lists/subscribe 2007/6/14, Manso Gomez, Ramon <[EMAIL PROTECTED]>: How can delete my suscription to this forums? -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] En nombre de Jyoti Seth Enviado el: jueves, 14 de junio de 2007 8:04 Para: 'Pavel Stehule' CC: pgsql-general@postgresql.org; [EMAIL PROTECTED] Asunto: Re: [SQL] setof or array as input parameter to postgresql 8.2 functions Thanks a lot. Regards, Jyoti -Original Message- From: Pavel Stehule [mailto:[EMAIL PROTECTED] Sent: Thursday, June 14, 2007 11:27 AM To: Jyoti Seth Cc: pgsql-general@postgresql.org; [EMAIL PROTECTED] Subject: Re: [SQL] setof or array as input parameter to postgresql 8.2 functions 2007/6/14, Jyoti Seth <[EMAIL PROTECTED]>: > Thanks for the solution. With this I am able to pass arrays and > multidimensional arrays in postgresql functions. > > One of my problem is still left I want to pass set of values with different > datatypes.For eg: > I want to pass following values to the function: > 1 ajay 1000.12 > 2 rita 2300.24 > 3 leena 1230.78 > 4 jaya 3432.45 > > As the values have different data types I have to create three > different arrays. Is there any way with which I can pass this as a > single setof values. > > You have to wait for 8.3 where arrays on composite types are supported. Currently in one our application we use array of arrays where different types are too, and we cast it to text. Regards Pavel ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Fault Tolerant Postgresql (two machines, two postmasters, one disk array)
On May 11, 12:08 pm, [EMAIL PROTECTED] ("Joshua D. Drake") wrote:
> Geoffrey wrote:
>
> People still use OpenVMS? ... elitist isn't the word I would choose ;)
>
Not only do they use it, new books get written about doing application
development with it. It is still the only OS able to create a fault
tolerant world-wide cluster with complete transaction management
across all nodes. Not just database transactions, but file level and
message queue all integrated with one transaction manager.
Take a look at http://www.theminimumyouneedtoknow.com for information
about "The Minimum You Need to Know to Be an OpenVMS Application
Developer"
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] how to speed up query
and script terminates after 5.5 hours running yesterday night. This might be a good reason not to run the script as a single long transaction --- it's probably accumulating locks on a lot of different tables. Which would be fine if it was the only thing going on, but evidently it isn't. Thank you. I removed transaction from script as all. I send every statement separately to Postgres. So each statement runs in its own single transaction now. I hope that this does not decrease speed and this is best solution? Now explain analyze SELECT r.dokumnr FROM rid r LEFT JOIN dok d USING (dokumnr) WHERE d.dokumnr IS NULL returs the same time from script and when returned separately in small database. Andrus. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] ANN: Linq provider for PostgreSQL
Dear group, I would like to announce a Linq Provider for Postgres (i.e. a driver for C# 3.0 Language-Integrated Query - which is an O/R mapping layer). Here is the project page: http://code2code.net/DB_Linq/index.html Or go straight to the code: http://code.google.com/p/dblinq2007/ Limitations: a) You need to run from source, and b) it's not ready for production yet. Regards, Jiri George Moudry ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] PostGreSQL for a small Desktop Application
On 14 Giu, 12:38, [EMAIL PROTECTED] (Marco Colombo) wrote: > While PG has tons more features than SQLite, the major question here is: > do you really need a database _server_? One thing that PG is designed > for is handling many (as in 100) concurrent users. Database users, that > is, meaning processes (running on different computers) opening a > connection and issueing queries. > > Of course, it handles it very well also when those processes all run on > a single server (and all connections are local connections), such as an > HTTP server running, say, PHP. That model is very similar to the > distributed one, since there's no state shared by the httpd/PHP > processes. All shared state is inside the database server. It also > happens to be persistant. > > [.] > > But you mentioned using C#/.Net. AFAIK (but I'm no expert) that's yet a > different model. You have a single process (although very likely > multithreaded) which is able to hold a shared state while serving > concurrent clients. Here, a database is "just" a backend for persistent > state (that it, across reboots or crashes). Any good (thread-safe) > library that writes to files would do. If you need/want SQL, SQLite > comes into play. Actually, this is what it was designed for. It's much > easier to install (it's all in a .dll) and administer (close to zero > administration I think) than PostgreSQL (or any RDBMS). For such an use, > PG would surely do, but may be just overkill. Good advices. My issue here is that i will surely need to access to the same database from different computer, not many, maybe just a couple but i can't size my scope to a single machine. Reading inside SQLite documentation i found this: "People who have a lot of experience with Windows tell me that file locking of network files is very buggy and is not dependable. If what they say is true, sharing an SQLite database between two or more Windows machines might cause unexpected problems." http://www.sqlite.org/faq.html#q7 I do prefer to avoid this kind of problem, as mostly my user base will run on Windows machine. I may probably use SQLite to synchronize server data with notebooks to allow offline work, but for the main system even if i do not need the performance (i don't need scalability and i'm not going high volume) of a real data server, i do need its reliability handling concurrency. If i do have two or three users using same data at the same time (typical use) i need to trust the data system to correctly handle requests. Lastly, i will surely take an inner look on ORM i may enjoy the possibility to switch database system, this way i may not only easily switch between online and offline mode but even allow my users to easily install my app in a "single computer" mode (not unusual) as opposed to normal configuration. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] how to speed up query
from pgAdmin, it takes 1 second. When I run this command from script it takes 11 minutes! Any idea why running this command from script takes 11 minutes? Different plans maybe? Try EXPLAIN ANALYZE in both cases. Thank you. I tried explain analyze SELECT r.dokumnr FROM rid r LEFT JOIN dok d USING (dokumnr) WHERE d.dokumnr IS NULL with small database. From script this command returns Hash Left Join (cost=12.11..60.42 rows=1 width=4) (actual time=105.473..105.473 rows=0 loops=1) Hash Cond: (r.dokumnr = d.dokumnr) Filter: (d.dokumnr IS NULL) -> Seq Scan on rid r (cost=0.00..38.87 rows=687 width=4) (actual time=2.144..90.823 rows=687 loops=1) -> Hash (cost=10.38..10.38 rows=138 width=4) (actual time=13.925..13.925 rows=138 loops=1) -> Seq Scan on dok d (cost=0.00..10.38 rows=138 width=4) (actual time=1.715..13.812 rows=138 loops=1) Total runtime: 105.542 ms running in standalone it returns Hash Left Join (cost=13.44..61.76 rows=1 width=4) (actual time=2.172..2.172 rows=0 loops=1) Hash Cond: (r.dokumnr = d.dokumnr) Filter: (d.dokumnr IS NULL) -> Seq Scan on rid r (cost=0.00..38.87 rows=687 width=4) (actual time=0.076..0.802 rows=687 loops=1) -> Hash (cost=11.53..11.53 rows=153 width=4) (actual time=0.400..0.400 rows=138 loops=1) -> Seq Scan on dok d (cost=0.00..11.53 rows=153 width=4) (actual time=0.013..0.242 rows=138 loops=1) Total runtime: 2.338 ms I have no idea why this command runs 50 times slower in script. ODBC driver inserts RELEASE SAVEPOINT and SAVEPOINT commands before every statement. There is great explanation about his in http://archives.postgresql.org/pgsql-odbc/2006-05/msg00078.php Unfortunately, no connection string option is documented. I havent found a way to disable this automatic SAVEPOINT insertion from odbc connection string. I havent got reply to my message from January, 18 2007 in odbc forum (I posted again today). Reading ODBC driver source this I expected that Protocol=-0 in connection string should work but this does not. Probably I missed something in C source. However I think that this cannot slow down SELECT command speed. Do you have work_mem set the same in both cases? Yes. I have same database server and same database. Andrus. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] allocate chunk of sequence
hello,
I try to allocate a chunk of ids from a sequence with the following
proc. However, if I don't use the 'lock lock_table', the proc may not
work when it runs at the same time by different psql sessions. Is there
a better way without using the 'lock lock_table' ?
Thanks,
Gary
create or replace function proc_allocate_seq(int)
returns int as $$
declare
nNumberOfFiles alias for $1;
aFileId int;
aNewFileId int;
begin
lock lock_table;
aFileId = nextval('aa_seq');
; sleep(3);if you have the proc
aNewFileId = setval('aa_seq', aFileId + nNumberOfFiles - 1);
return aFileId;
end;
$$ language plpgsql;
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[GENERAL] Disable duplicate index creation
PostgreSQL runs commands create table test ( test integer primary key ); create index i1 on test(test); create index i2 on test(test); without any error. Now there are 3 same indexes on table. How to fix this so that duplicate indexes are not allowed ? Andrus. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] [GENERAL] psql : Error: Cannot stat /pgdata/8.2/main
On Wed, Jun 13, 2007 at 09:45:52AM -0400, Tom Lane wrote: > Joost Kraaijeveld <[EMAIL PROTECTED]> writes: > > I have moved my database files from their default location to their own > > partition on with their own controller and disks. PostgreSQL works OK > > and I can connect with Pgadmin (Debian Lenny AMD64, PostgreSQL 8.2.4). > > > When I want to connect with psql however (with a non-root account) I get > > the following: > > > panoramix:~$ psql -d my_database > > Error: Cannot stat /pgdata/8.2/main > > > /pgdata/8.2/main is the location where the database files are actually > > located. > > psql itself has no business touching the database directory, and a quick > search of the source code shows no instance of "Cannot stat" anywhere in > released PG sources. > > I think you are being burnt by some misbehavior of Debian's wrapper > patches, and a complaint directed there is the next step for you. > It shows up a couple of times in the Debian wrapper scripts, when it's trying to figure out which socket to actually connect to. It only gets there after giving up on reading postgresql.conf and after giving up on /var/run/postgresql. From the bug Joost filed in the Debian bts[1], it looks like the Debian upgrade script got confused somehow. Cheers, Pasc [1]: http://bugs.debian.org/428698 -- Pascal Hakim Do not bend ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Historical Data Question
Can anyone help me with this problem? I have a table in my database that holds information on policies and this table is linked to a number of other tables. I need to be able to keep a history of all changes to a policy over time. The other tables that are linked to policy also need to store historical data. When I run a query on the policy table for a certain period, I also need to be able to pull the correct related rows (i.e. the information that would have been in the table at that time) from the tables linked to it. Does anyone have any suggestions on how to store historical information in databases? Any good resources (books, etc..) that cover this information? Thanks for your time. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Disable duplicate index creation
"Andrus" <[EMAIL PROTECTED]> writes: > PostgreSQL runs commands > create table test ( test integer primary key ); > create index i1 on test(test); > create index i2 on test(test); > without any error. > Now there are 3 same indexes on table. > How to fix this so that duplicate indexes are not allowed ? Simple: don't do that. Having the system try to prevent this has been proposed, and rejected, before. The main argument against has been that it'd prevent some occasionally-useful procedures. Instead of REINDEX, which locks out use of the index (for a long time, if table is big), you can make a new index in parallel with CREATE INDEX CONCURRENTLY, and then drop the old index, and not deny service to any query for very long. Another objection is that it's not always clear which indexes are redundant --- eg, should we forbid indexes on both (a,b) and (b,a)? In your above example the indexes aren't even really identical --- the pkey one is unique and the others not. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pg_restore out of memory
Francisco Reyes <[EMAIL PROTECTED]> writes: > Tom Lane writes: >> Can we see the context-sizes dump that should've come out right before >> that in the log? > Is this the one you need? No. [squints...] Hm, you're looking at a syslog log, aren't you. The memory dump only comes out on stderr (I think because of paranoia about running out of memory while trying to report we're out of memory). Can you get the postmaster's stderr output? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Historical Data Question
On Thu, 14 Jun 2007, Lza wrote: When I run a query on the policy table for a certain period, I also need to be able to pull the correct related rows (i.e. the information that would have been in the table at that time) from the tables linked to it. Check out "Developing Time-Oriented Database Applications in SQL" by Richard Snodgras; it's an entire book devoted to this and related topics. It's out of print and hard to get, but you can download a free PDF copy from the author at http://www.cs.arizona.edu/people/rts/publications.html Much of the text shows how to simulate types and operations that now are built-in to PostgreSQL, like the interval type, so it's not quite as intimidating a read as it seems at first; there's a lot of code for older databases that you can completely ignore. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Historical Data Question
am Thu, dem 14.06.2007, um 10:57:43 -0700 mailte Lza folgendes: > Can anyone help me with this problem? > > I have a table in my database that holds information on policies and > this table is linked to a number of other tables. I need to be able to > keep a history of all changes to a policy over time. The other tables > that are linked to policy also need to store historical data. When I > run a query on the policy table for a certain period, I also need to > be able to pull the correct related rows (i.e. the information that > would have been in the table at that time) from the tables linked to > it. > > Does anyone have any suggestions on how to store historical > information in databases? Any good resources (books, etc..) that cover > this information? Maybe this one: http://www.rueping.info/doc/Andreas%20R&ping%20--%202D%20History.pdf Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] how to speed up query
On Jun 13, 3:13 pm, "Andrus" <[EMAIL PROTECTED]> wrote:
(...)
> As I understand, only way to optimize the statement
>
> delete from firma1.rid where dokumnr not in (select dokumnr from
> firma1.dok);
>
> assuming that firma1.dok.dokumnr does not contain null values is to change
> it to
>
> CREATE TEMP TABLE mydel AS
> SELECT r.dokumnr
> FROM rid r
> LEFT JOIN dok d USING (dokumnr)
> WHERE d.dokumnr IS NULL;
> DELETE FROM rid USING mydel WHERE rid.dokumnr =mydel.dokumnr;
> drop table mydel;
As I mentioned when I proposed it, the temp table may not even be
necessary. The important part is the LEFT JOIN instead of the NOT IN
(as Martijn has explained).
You could try the direct approach ...
DELETE FROM rid
USING ( SELECT r.dokumnr
FROM rid r
LEFT JOIN dok d USING (dokumnr)
WHERE d.dokumnr IS NULL) x
WHERE rid.dokumnr = x.dokumnr;
... and see which runs faster. Probably it does not make much of a
difference.
If the temp table works for you, you might be interested in a new
feature of 8.2: CREATE TEMP TABLE AS ...ON COMMIT DROP;
http://www.postgresql.org/docs/current/static/sql-createtableas.html
(...)
> explain analyze SELECT r.dokumnr
> FROM rid r
> LEFT JOIN dok d USING (dokumnr)
> WHERE d.dokumnr IS NULL
>
> returns
>
> "Hash Left Join (cost=7760.27..31738.02 rows=1 width=4) (actual
> time=2520.904..2520.904 rows=0 loops=1)"
> " Hash Cond: (r.dokumnr = d.dokumnr)"
> " Filter: (d.dokumnr IS NULL)"
> " -> Seq Scan on rid r (cost=0.00..17424.24 rows=202424 width=4) (actual
> time=0.032..352.225 rows=202421 loops=1)"
> " -> Hash (cost=6785.01..6785.01 rows=56101 width=4) (actual
> time=211.150..211.150 rows=56079 loops=1)"
> "-> Seq Scan on dok d (cost=0.00..6785.01 rows=56101 width=4)
> (actual time=0.021..147.805 rows=56079 loops=1)"
> "Total runtime: 2521.091 ms"
If the indices are present (and visible) at the time of execution, as
you described it, we should be seeing index scans on dok_dokumnr_idx
and rid_dokumnr_idx instead of sequential scans.
That's what I get on a similar query in one of my databases:
EXPLAIN ANALYZE SELECT a.adr_id FROM cp.adr a LEFT JOIN cp.kontakt k
USING (adr_id) WHERE k.adr_id IS NULL;
Merge Left Join (cost=0.00..1356.31 rows=10261 width=4) (actual
time=0.096..56.759 rows=3868 loops=1)
Merge Cond: ("outer".adr_id = "inner".adr_id)
Filter: ("inner".adr_id IS NULL)
-> Index Scan using adr_pkey on adr a (cost=0.00..947.54
rows=10261 width=4) (actual time=0.012..23.118 rows=10261 loops=1)
-> Index Scan using kontakt_adr_id_idx on kontakt k
(cost=0.00..295.47 rows=7011 width=4) (actual time=0.007..13.299
rows=7011 loops=1)
Total runtime: 58.510 ms
Regards
Erwin
---(end of broadcast)---
TIP 6: explain analyze is your friend
