Re: [GENERAL] Fresh install on Mac OS 10.5.4
Thanks guys! Yes, 8.3.3 binaries got installed in /usr/local/pgsql/bin and so the 7.3 version that I had previously didn't get overwritten. Binaries for 7.3 were in /usr/local/bin Works now. Regards, Manoj Patwardhan On Aug 3, 2008, at 12:17 AM, Tom Lane wrote: John DeSoi <[EMAIL PROTECTED]> writes: On Aug 2, 2008, at 7:13 PM, Manoj Patwardhan wrote: bash-3.2$ initdb -D /usr/local/pgsql/data dyld: Library not loaded: /usr/local/pgsql/lib/libpq.4.dylib Referenced from: /usr/local/bin/initdb Could be a path problem. Try Yeah ... the reference to libpq major version 4 suggests *very* strongly that this copy of initdb is from PG version 8.0 or 8.1. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] issues with java driver setDate() on function call
"Ismael " <[EMAIL PROTECTED]> writes: > I have a function declared as follows > CREATE OR REPLACE FUNCTION insertaegreso(usuario1 integer, importepago1 > numeric, fechapago1 DATE, concepto1 character varying, tipopagonomina1 > character varying, comentarios1 character varying) > RETURNS integer ASimplementation > but when I try to call it using java's PreparedStatement pst; > I get this error, (note: "no existe la función" means "the function > doesn't exists") > org.postgresql.util.PSQLException: ERROR: no existe la función > insertaegreso(integer, double precision, unknown, character varying, > character varying, character varying) Actually I think your problem is with the *second* parameter. There is no implicit cast from double precision to numeric. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Fresh install on Mac OS 10.5.4
John DeSoi <[EMAIL PROTECTED]> writes: > On Aug 2, 2008, at 7:13 PM, Manoj Patwardhan wrote: >> bash-3.2$ initdb -D /usr/local/pgsql/data >> dyld: Library not loaded: /usr/local/pgsql/lib/libpq.4.dylib >> Referenced from: /usr/local/bin/initdb > Could be a path problem. Try Yeah ... the reference to libpq major version 4 suggests *very* strongly that this copy of initdb is from PG version 8.0 or 8.1. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Fresh install on Mac OS 10.5.4
On Aug 2, 2008, at 7:13 PM, Manoj Patwardhan wrote: bash-3.2$ initdb -D /usr/local/pgsql/data dyld: Library not loaded: /usr/local/pgsql/lib/libpq.4.dylib Referenced from: /usr/local/bin/initdb Reason: image not found Trace/BPT trap Any ideas? This is on Mac OS 10.5.4. What I see in /usr/local/pgsql/ lib is libpq.5.dylib and not libpq.4.dylib. Could be a path problem. Try which initdb initdb --version to make sure you are running the version of initdb you think you are. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Fresh install on Mac OS 10.5.4
I downloaded version 8.3.3 and after a successful build from source and an install, I tried to create a database cluster. I got the following error: bash-3.2$ initdb -D /usr/local/pgsql/data dyld: Library not loaded: /usr/local/pgsql/lib/libpq.4.dylib Referenced from: /usr/local/bin/initdb Reason: image not found Trace/BPT trap Any ideas? This is on Mac OS 10.5.4. What I see in /usr/local/pgsql/ lib is libpq.5.dylib and not libpq.4.dylib. Thanks! Manoj Patwardhan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is there any reason why "edit PostgreSQL.conf should be on my menu"
On Sat, Aug 2, 2008 at 9:14 AM, johnf <[EMAIL PROTECTED]> wrote: > I wonder if he talking about pgadmin3? It's on the menu. Only J.M. knows for sure... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Advice on implementing counters in postgreSQL
Marco Bizzarri wrote: > Hi all. > > I need to keep a numer of counters in my application; my counters are > currently stored in a table: > > name | next_value | year > > > The counters must be progressive numbers with no holes in between > them, and they must restart from 1 every year. What I've done so far > is to access them while in SERIALIZABLE ISOLATION LEVEL, with the > following: > > SELECT next_value FROM counters WHERE name = 'name' for update; > UPDATE counters SET next_value = next_value + 1 WHERE name = 'name'; >... > 2) while this works, it has the unfortunate behaviour to cause > conflict between concurrent transactions; so, one of them has to be > restarted and redone from scratch. Is there a way to avoid this > behaviour? maybe with lock to tables? The way I understand the documentation at "http://www.postgresql.org/docs/8.3/static/transaction-iso.html"; and 'http://www.postgresql.org/docs/current/static/explicit-locking.html', you should not have to use the serial isolation level. I would define the counter table so as to hold the last-used value, rather that the "next" value, and then do the UPDATE first. As a consequence, assuming all this happens within a transaction of course, the SELECT FOR UPDATE syntax is not required either because the UPDATE will grab a lock on the row and block other updates until the transaction is finished. That is, concurrency is protected and you don't have to restart any transactions because subsequent transactions will just wait until the first one finishes due to nature of the lock automatically acquired by the initial UPDATE statement. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] issues with java driver setDate() on function call
sorry, just discovered the answer, only need to cast the value like this String sql = "SELECT * FROM insertaEgreso(?, ?, ?::DATE, ?, ?, ?)"; pst.setDate(3, date); :) > > > I have a function declared as follows > CREATE OR REPLACE FUNCTION insertaegreso(usuario1 integer, importepago1 > numeric, fechapago1 DATE, concepto1 character varying, tipopagonomina1 > character varying, comentarios1 character varying) > RETURNS integer ASimplementation > > notice the field "fechapago1 DATE" > > the function is working just fine, I tried it from pgadmin3 and it works, > but when I try to call it using java's PreparedStatement pst; > pst.setDate(3, date); > I get this error, (note: "no existe la función" means "the function > doesn't exists") > > org.postgresql.util.PSQLException: ERROR: no existe la función > insertaegreso(integer, double precision, unknown, character varying, > character varying, character varying) > > it puts "unknown" instead of date > > > is there a workaround for it that doesn't involve creating a function with > VARCHAR instead of DATE? > > > because also when I try to do something like > > String sql1 = "SELECT EXTRACT (YEAR FROM ?)"; > PreparedStatement pst = con.getPreparedStatement(sql1); > pst.setDate(1, date); > > I get this error: > > (the functionisn't unique) > org.postgresql.util.PSQLException: ERROR: la función > pg_catalog.date_part(unknown, unknown) no es única > > > tanks in advance > _ > Plug&Play te trae en exclusiva los mejores conciertos de la red > http://club.prodigymsn.com/ > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general _ Plug&Play te trae en exclusiva los mejores conciertos de la red http://club.prodigymsn.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] issues with java driver setDate() on function call
I have a function declared as follows CREATE OR REPLACE FUNCTION insertaegreso(usuario1 integer, importepago1 numeric, fechapago1 DATE, concepto1 character varying, tipopagonomina1 character varying, comentarios1 character varying) RETURNS integer ASimplementation notice the field "fechapago1 DATE" the function is working just fine, I tried it from pgadmin3 and it works, but when I try to call it using java's PreparedStatement pst; pst.setDate(3, date); I get this error, (note: "no existe la función" means "the function doesn't exists") org.postgresql.util.PSQLException: ERROR: no existe la función insertaegreso(integer, double precision, unknown, character varying, character varying, character varying) it puts "unknown" instead of date is there a workaround for it that doesn't involve creating a function with VARCHAR instead of DATE? because also when I try to do something like String sql1 = "SELECT EXTRACT (YEAR FROM ?)"; PreparedStatement pst = con.getPreparedStatement(sql1); pst.setDate(1, date); I get this error: (the functionisn't unique) org.postgresql.util.PSQLException: ERROR: la función pg_catalog.date_part(unknown, unknown) no es única tanks in advance _ Plug&Play te trae en exclusiva los mejores conciertos de la red http://club.prodigymsn.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Initdb problem on debian mips cobalt: Bus error
Hi chaps, I'm still trying to get postgres working on my qube2 with debian. I've downgraded it to sarge, and it's a little better, however now when compiling I get "functions.o: file not recognized: File truncated" http://privatepaste.com/3f1j0tI0ow Any ideas? --- On Tue, 22/7/08, Glyn Astill <[EMAIL PROTECTED]> wrote: > From: Glyn Astill <[EMAIL PROTECTED]> > Subject: Re: [GENERAL] Initdb problem on debian mips cobalt: Bus error > To: "Tom Lane" <[EMAIL PROTECTED]> > Cc: "Stefan Kaltenbrunner" <[EMAIL PROTECTED]>, pgsql-general@postgresql.org > Date: Tuesday, 22 July, 2008, 7:35 PM > > > > The stack size rlimit looks normal, which makes a > crash in > > this spot > > look even less probable. I think maybe you are > looking at > > a stale > > corefile that doesn't quite correspond to this > postgres > > executable. > > > > You are correct. I just checked and the core file was > created on the 18th, that must be from the first attempt to > run make check. I just assumed that the next time I > attempted to run make check it'd be overwriting it, and > that's obviously not the case. > > I'll try and get it to generate a fresh file. > > > > __ > Not happy with your email address?. > Get the one you really want - millions of new email > addresses available now at Yahoo! > http://uk.docs.yahoo.com/ymail/new.html > > -- > Sent via pgsql-general mailing list > (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general __ Not happy with your email address?. Get the one you really want - millions of new email addresses available now at Yahoo! http://uk.docs.yahoo.com/ymail/new.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] non-WAL btree?
On Fri, Aug 1, 2008 at 4:49 PM, Alex Vinogradovs <[EMAIL PROTECTED]> wrote: > It's all about number of repetions. If say I load my table > with 50k every minute, and run reindex every minute, how > long do you think it would take by end of the day, when > my table (it's daily partition actually) is at maximum > capacity ? And database may actually never crash, and > I won't have to run reindex at all ;) > maybe http://www.postgresql.org/docs/8.3/static/wal-async-commit.html is what you need... begin; set local synchronous_commit to off; insert... insert... ... commit; or set synchronous_commit to off; copy command; set synchronous_commit to on; -- regards, Jaime Casanova Soporte y capacitación de PostgreSQL Guayaquil - Ecuador Cel. (593) 87171157 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is there any reason why "edit PostgreSQL.conf should be on my menu"
On Saturday 02 August 2008 01:12:35 am Craig Ringer wrote: > John Meyer wrote: > > Especially when I haven't edited anything yet? > > You might want to tell the readers here just a *little* bit more about > your problem. > > Start with "what menu?!?". > > More seriously: > > - What is your operating system > - What is the version of your operating system > - What version of PostgreSQL do you have > - How and from where did you obtain and install that version > > ... and what menu, exactly, are you talking about? Given your post I > have to randomly guess "the Start menu in Windows XP or Vista" ... but > that's very much a guess. > > Remember, nobody (except perhaps Tom Lane) around here is psychic. They > don't know what you don't tell them, so you need to provide full and > detailed explanations in your posts. > > -- > Craig Ringer I wonder if he talking about pgadmin3? It's on the menu. -- John Fabiani -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Advice on implementing counters in postgreSQL
On Sat, Aug 02, 2008 at 09:23:31AM +0200, Marco Bizzarri wrote: > Hi all. > > I need to keep a numer of counters in my application; my counters > are currently stored in a table: > > name | next_value | year > > The counters must be progressive numbers with no holes in between > them, and they must restart from 1 every year. Here's a backward-compatible way to do this: http://www.varlena.com/GeneralBits/130.php Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Advice on implementing counters in postgreSQL
On Sat, Aug 2, 2008 at 11:04 AM, Craig Ringer <[EMAIL PROTECTED]> wrote: > Marco Bizzarri wrote: >> Thanks for the advice, Craig. >> >> I'm on a number of different PostgreSQL versions, ranging from 7.4 to >> 8.3, so I've to retain, where possible, compatibility with older >> versions. >> >> Is this better on a transaction/serialization point of view? > > As far as I know it's not significantly different, though I expect it'd > be somewhat more efficient. However, support for UPDATE ... RETURNING > was only added in 8.2 (or somewhere around there) anyway, so if you need > to work with old versions like 7.4 it's no good to you anyway. > > I take it there's no way you can present the gapless identifiers at the > application level, leaving the actual tables with nice SEQUENCE > numbering? Or, alternately, insert them by timestamp/sequence (leaving > the user-visible ID null) then have another transaction come back and > assign them their gapless numeric identifiers in a single simple pass later? > You're really going to suffer on concurrency if you have to acquire > values from a gapless sequence as part of a transaction that does much > other work. Well, the sequence must be gapless, because it is an implementation of a law regarding how documents must be recorded when they are received or sent in a public administration. I can accept a "degraded" performance in this topic, considering that usually, I've between 200 and 1000 documents recorded (i.e. numbered) in a day, which is not such a great number. However, I would avoid as much as possible serialization errors, which would force me to repeat the transaction. I'm experimenting with LOCK counters IN EXCLUSIVE MODE; it seems it is able to rip me of thos serialization errors. Do you see any problems in this? Regards Marco -- Marco Bizzarri http://iliveinpisa.blogspot.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] why so many error when I load the data to database from a script which generated by pg_dump.
On 02/08/2008 07:22, Yi Zhao wrote: CONTEXT: COPY htmlcontent, line 312807: "1207327 "-//W3C//DTD HTML 4.0 Transitional//EN">\n\n\n Would there perchance be tabs embedded in the HTML that might confuse COPY? Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is there any reason why "edit PostgreSQL.conf should be on my menu"
> Especially when I haven't edited anything yet? Because you might want to. -- GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen! Jetzt dabei sein: http://www.shortview.de/[EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Advice on implementing counters in postgreSQL
Marco Bizzarri wrote: > Thanks for the advice, Craig. > > I'm on a number of different PostgreSQL versions, ranging from 7.4 to > 8.3, so I've to retain, where possible, compatibility with older > versions. > > Is this better on a transaction/serialization point of view? As far as I know it's not significantly different, though I expect it'd be somewhat more efficient. However, support for UPDATE ... RETURNING was only added in 8.2 (or somewhere around there) anyway, so if you need to work with old versions like 7.4 it's no good to you anyway. I take it there's no way you can present the gapless identifiers at the application level, leaving the actual tables with nice SEQUENCE numbering? Or, alternately, insert them by timestamp/sequence (leaving the user-visible ID null) then have another transaction come back and assign them their gapless numeric identifiers in a single simple pass later? You're really going to suffer on concurrency if you have to acquire values from a gapless sequence as part of a transaction that does much other work. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Advice on implementing counters in postgreSQL
Thanks for the advice, Craig. I'm on a number of different PostgreSQL versions, ranging from 7.4 to 8.3, so I've to retain, where possible, compatibility with older versions. Is this better on a transaction/serialization point of view? Regards Marco On Sat, Aug 2, 2008 at 10:19 AM, Craig Ringer <[EMAIL PROTECTED]> wrote: > Marco Bizzarri wrote: >> Hi all. >> >> I need to keep a numer of counters in my application; my counters are >> currently stored in a table: >> >> name | next_value | year >> >> >> The counters must be progressive numbers with no holes in between >> them, and they must restart from 1 every year. What I've done so far >> is to access them while in SERIALIZABLE ISOLATION LEVEL, with the >> following: >> >> SELECT next_value FROM counters WHERE name = 'name' for update; >> UPDATE counters SET next_value = next_value + 1 WHERE name = 'name'; > > If you're using a sufficiently recent version of Pg you can use: > > UPDATE counters > SET next_value = next_value + 1 > WHERE name = 'name' > RETURNING next_value; > > instead, which is slightly nicer. It'll return the *new* value of > `next_value', so you'd have to make a few tweaks. > > -- > Craig Ringer > -- Marco Bizzarri http://iliveinpisa.blogspot.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Advice on implementing counters in postgreSQL
Marco Bizzarri wrote: > Hi all. > > I need to keep a numer of counters in my application; my counters are > currently stored in a table: > > name | next_value | year > > > The counters must be progressive numbers with no holes in between > them, and they must restart from 1 every year. What I've done so far > is to access them while in SERIALIZABLE ISOLATION LEVEL, with the > following: > > SELECT next_value FROM counters WHERE name = 'name' for update; > UPDATE counters SET next_value = next_value + 1 WHERE name = 'name'; If you're using a sufficiently recent version of Pg you can use: UPDATE counters SET next_value = next_value + 1 WHERE name = 'name' RETURNING next_value; instead, which is slightly nicer. It'll return the *new* value of `next_value', so you'd have to make a few tweaks. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is there any reason why "edit PostgreSQL.conf should be on my menu"
John Meyer wrote: > Especially when I haven't edited anything yet? You might want to tell the readers here just a *little* bit more about your problem. Start with "what menu?!?". More seriously: - What is your operating system - What is the version of your operating system - What version of PostgreSQL do you have - How and from where did you obtain and install that version ... and what menu, exactly, are you talking about? Given your post I have to randomly guess "the Start menu in Windows XP or Vista" ... but that's very much a guess. Remember, nobody (except perhaps Tom Lane) around here is psychic. They don't know what you don't tell them, so you need to provide full and detailed explanations in your posts. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Advice on implementing counters in postgreSQL
Hi all. I need to keep a numer of counters in my application; my counters are currently stored in a table: name | next_value | year The counters must be progressive numbers with no holes in between them, and they must restart from 1 every year. What I've done so far is to access them while in SERIALIZABLE ISOLATION LEVEL, with the following: SELECT next_value FROM counters WHERE name = 'name' for update; UPDATE counters SET next_value = next_value + 1 WHERE name = 'name'; of course, if I do not find the counter, I create it (which automatically happens at the begin of a new year). This seems to work to me, but I've two questions: 1) is there any scenario which I'm missing here and which could lead me to troubles? Deadlocks excluded. 2) while this works, it has the unfortunate behaviour to cause conflict between concurrent transactions; so, one of them has to be restarted and redone from scratch. Is there a way to avoid this behaviour? maybe with lock to tables? Thanks you all for your attention Regards Marco -- Marco Bizzarri http://iliveinpisa.blogspot.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
