Re: [GENERAL] INTERVAL SECOND limited to 59 seconds?
Sebastien FLAESCH wrote: Actually it's not limited to the usage of INTERVAL SECOND, I am writing a PostgreSQL driver for our 4GL virtual machine... I need to store all possible Informix INTERVAL types such as: INTERVAL MONTH(8) TO MONTH INTERVAL DAY(8) TO MINUTE INTERVAL SECOND TO FRACTION(5) In Postgres, you should just store it as an INTERVAL which (unlike some other RDBMS') has the ability to store ranges from fractional seconds to thousands of years. Then if you need to output it in the above format, make a view that splits the actual interval into month, minute and fractional second pieces. -- 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] origins/destinations
I m new to PostgreSQL so please tell me the drawbacks of is this solution Your Table 3 should not be a table it should be a array. CREATE TABLE table3 ( ori_des int[][] ); for origin area_n and destination area_m if count is k, then ori_des[n][m] = k. --- On Tue, 19/5/09, Carson Farmer wrote: From: Carson Farmer Subject: [GENERAL] origins/destinations To: pgsql-general@postgresql.org Cc: "Carson Farmer" Date: Tuesday, 19 May, 2009, 10:27 PM Hi list, I have (what I thought was) a relatively simple problem, but my knowledge of sql is just not good enough to get this done: I have a table which is basically a number of individuals with both their origin and destination as columns (see Table 1). In this case, origins and destinations are the census area in which they and work. What I would like to do is generate an nxn matrix (preferably output to csv but I'll take what I can get), where origins are on the y axis, and destinations on the x axis (see Table 3). I can already group by both origins and destinations to produce Table 2, but I don't know what steps are needed to get to Table 3. Any help or suggestions are greatly appreciated! Table 1 id | origin | destination 1 area1 area5 2 area1 area5 3 area1 area5 4 area2 area4 5 area4 area2 6 area5 area5 7 area2 area4 8 area2 area4 9 area4 area3 10 area3 area5 Table 2 id | origin | destination | count 1 area1 area5 3 4 area2 area4 3 5 area4 area2 1 6 area5 area5 1 9 area4 area3 1 10 area3 area5 1 Table 3 origins | area1 | area2 | area3 | area4 | area5 | ... area1 0 0 0 0 3 area2 0 0 0 3 0 area3 0 0 0 0 1 area4 0 1 1 0 0 area5 0 0 0 0 1 Regards, Carson -- Carson J. Q. Farmer ISSP Doctoral Fellow National Centre for Geocomputation (NCG), Email: carson.far...@gmail.com Web: http://www.carsonfarmer.com/ http://www.ftools.ca/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Get an email ID as yourn...@ymail.com or yourn...@rocketmail..com. Click here http://in.promos.yahoo.com/address
Re: [GENERAL] Configure fails to find readline libraries
Jeffrey Trimble writes: > configure:6337: checking for -lreadline > configure:6364: gcc -o conftest -I/usr/local/include -I/usr/local/ > include/readline -Wall -Wmissing-prototypes -Wpointer-arith -Winline - > Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing - > fwrapv -D_GNU_SOURCE -I/usr/local/include -I/usr/local/include/ > readline -L/usr/local/lib -L/usr/local/lib conftest.c -lreadline - > lcrypt -ldl -lm >&5 > /usr/local/lib/libreadline.so: undefined reference to `PC' > /usr/local/lib/libreadline.so: undefined reference to `tgetflag' > /usr/local/lib/libreadline.so: undefined reference to `tgetent' You should keep reading past that point ... The missing symbols here can come from termcap or ncurses. There is code in our configure script to try linking readline with those additional libraries. What's not clear is why those attempts failed, but you didn't show us the trace of them failing. 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] Get block of N numbers from sequence
Merlin Moncure wrote:
> On Tue, May 19, 2009 at 9:32 AM, Thomas Guettler wrote:
>>
>> hubert depesz lubaczewski schrieb:
>>> On Tue, May 19, 2009 at 01:45:17PM +0200, Thomas Guettler wrote:
how can you get N numbers (without holes) from a sequence?
>>> alter sequence XXX increment by 1000;
>>> select nextval('XXX');
>>> alter sequence XXX increment by 1;
>> If other processes run nextval() between "increment by 1000" and "increment
>> by 1",
>> they leave big holes in the sequence.
>
> This is only works if everyone does it this way. If anybody throws a
> nextval() without locking the sequence first you have a race. Also,
> since alter sequence takes a full lock your concurrency is zero.
>
> Probably the best general way to attack this problem is using advisory
> locks. note the code below is untested.
If you want to be REALLY sure your sequence is never accessed without
being locked first, you can deny rights to access it to the usual users,
and write a Pl/PgSQL SECURITY DEFINER function to do all manipulation of
the sequence.
The sample function you posted could be trivially adjusted to operate
SECURITY DEFINER and would suit the purpose. Note that I haven't
examined this in great depth for security issues, and there may be
things I'm missing about the safe use of SECURITY DEFINER functions.
create or replace function my_nextval_for_seqname(
_count int, _v out bigint) returns bigint as
$$
declare
-- Hard code sequence name; we're running SECURITY DEFINER
-- and don't want the caller to be able to mess with any
-- sequence they choose to.
_seq text := 'seqname';
begin
if _count = 1 then
perform pg_advisory_lock_shared(999);
_v := nextval(_seq);
perform pg_advisory_unlock_shared(999);
else
perform pg_advisory_lock(999);
_v := nextval(_seq);
perform setval(_seq, _v + _count);
perform pg_advisory_unlock(999);
end if;
end;
$$ language plpgsql
VOLATILE
STRICT
SECURITY DEFINER;
--
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] Locking to restrict rowcounts.
-- From: "Richard Huxton" Of course, if you're going to have a separate table then you might as well store the count in there and actually update it on every insert/update/delete. Assuming you might find the count of some use somewhere. Set the fill-factor for the lock table and HOT should prevent the table bloating too. I think PERFORM * FROM items WHERE owner = name FOR UPDATE; sounds like it should work the best. What are the downsides for this that would require the further table of counts? FWIW items has a SERIAL primary key so FOR UPDATE should work on it. Shak -- 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] Direct I/O and postgresql version
On Tue, 19 May 2009, Pal, Dipali (UMKC-Student) wrote:
Which of the recent versions of postgresql support direct I/O?
As of 8.1 PostgreSQL does direct I/O for writes to the WAL if you've
configured wal_sync_method={open_datasync,open_sync} on supported
platforms. I know Linux works but Solaris doesn't. See
http://www.postgresql.org/docs/current/static/runtime-config-wal.html#GUC-WAL-SYNC-METHOD
and http://www.postgresql.org/docs/current/static/wal-configuration.html
for more details. The usual workaround for the Solaris problem is to
split the WAL pg_xlog directory onto another filesystem and change its
mounting options, see
http://blogs.sun.com/jkshah/entry/postgresql_wal_sync_method_and for more
about that.
There is no option to do direct writes for the main database I/O because
the database usually performs better if you rely on the filesystem cache.
You can certainly find situations where sync writes end up working out
better, but they're not common.
--
* Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD
--
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] Configure fails to find readline libraries
On Tue, May 19, 2009 at 7:32 PM, Jeffrey Trimble wrote: > Hi...Here's the output of that one: > ldd /usr/local/lib/libreadline.so > linux-gate.so.1 => (0xe000) > libc.so.6 => /lib/libc.so.6 (0xb7e9c000) > /lib/ld-linux.so.2 (0xb8055000) > ddev:/ # > Not really sure what the problem is then, it's probably obvious but I'm jet-lagged and running on low blood sugar /caffeine right now. There are two versions of readline. REadline 5.2 and Readline 6.0 Readline > 5.2 was installed during > the suse linux installation but it never installed the readline.h file. Go > figure. > The easiest way to deal with that is just to install the 'readline-devel' package via YaST. That may help. --Scott > > --Jeff > > Jeffrey Trimble > System LIbrarian > William F. Maag Library > Youngstown State University > 330.941.2483 (Office) > jtrim...@cc.ysu.edu > http://www.maag.ysu.edu > http://digital.maag.ysu.edu > > > > On May 19, 2009, at 7:28 PM, Scott Mead wrote: > > > On Tue, May 19, 2009 at 7:23 PM, Jeffrey Trimble wrote: > >> Hi.. thanks. >> Here's a printout from my find command: >> >> /usr/local/include/readline >> /usr/local/include/readline/readline.h >> >> >> And the plot thickens. Here is where it errors out and then loops >> endlessly until it quits: >> (There are symbolic links from libreadline.so to libreadline.so.6.0) >> >> configure:6240: $? = 0 >> configure:6243: test -s conftest >> configure:6246: $? = 0 >> configure:6316: result: none required >> configure:6337: checking for -lreadline >> configure:6364: gcc -o conftest -I/usr/local/include >> -I/usr/local/include/readline -Wall -Wmissing-prototypes -Wpointer-arith >> -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing >> -fwrapv -D_GNU_SOURCE -I/usr/local/include -I/usr/local/include/readline >> -L/usr/local/lib -L/usr/local/lib conftest.c -lreadline -lcrypt -ldl -lm >> >&5 >> > /usr/local/lib/libreadline.so: undefined reference to `PC' >> /usr/local/lib/libreadline.so: undefined reference to `tgetflag' >> /usr/local/lib/libreadline.so: undefined reference to `tgetent' >> /usr/local/lib/libreadline.so: undefined reference to `UP' >> /usr/local/lib/libreadline.so: undefined reference to `tputs' >> /usr/local/lib/libreadline.so: undefined reference to `tgoto' >> /usr/local/lib/libreadline.so: undefined reference to `tgetnum' >> /usr/local/lib/libreadline.so: undefined reference to `BC' >> /usr/local/lib/libreadline.so: undefined reference to `tgetstr' >> > >What happens if you run > >ldd /usr/local/lib/libreadline.so ? > > It looks like you've got either multiple versions of readline > installed and you're linking to the wrong one, or something is really wonky > with your readline build. A quick google is showing that all of those > symbols are in ncurses, which means that you should probably have ncurses in > your linker cache or linker runtime path. > >By the way, if you don't want readline functionality in psql (or care), > just build '--without-readline' > > --Scott > > >
Re: [GENERAL] Configure fails to find readline libraries
Hi...Here's the output of that one: ldd /usr/local/lib/libreadline.so linux-gate.so.1 => (0xe000) libc.so.6 => /lib/libc.so.6 (0xb7e9c000) /lib/ld-linux.so.2 (0xb8055000) ddev:/ # There are two versions of readline. REadline 5.2 and Readline 6.0 Readline 5.2 was installed during the suse linux installation but it never installed the readline.h file. Go figure. --Jeff Jeffrey Trimble System LIbrarian William F. Maag Library Youngstown State University 330.941.2483 (Office) jtrim...@cc.ysu.edu http://www.maag.ysu.edu http://digital.maag.ysu.edu On May 19, 2009, at 7:28 PM, Scott Mead wrote: On Tue, May 19, 2009 at 7:23 PM, Jeffrey Trimble wrote: Hi.. thanks. Here's a printout from my find command: /usr/local/include/readline /usr/local/include/readline/readline.h And the plot thickens. Here is where it errors out and then loops endlessly until it quits: (There are symbolic links from libreadline.so to libreadline.so.6.0) configure:6240: $? = 0 configure:6243: test -s conftest configure:6246: $? = 0 configure:6316: result: none required configure:6337: checking for -lreadline configure:6364: gcc -o conftest -I/usr/local/include -I/usr/local/ include/readline -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing - fwrapv -D_GNU_SOURCE -I/usr/local/include -I/usr/local/include/ readline -L/usr/local/lib -L/usr/local/lib conftest.c -lreadline - lcrypt -ldl -lm >&5 /usr/local/lib/libreadline.so: undefined reference to `PC' /usr/local/lib/libreadline.so: undefined reference to `tgetflag' /usr/local/lib/libreadline.so: undefined reference to `tgetent' /usr/local/lib/libreadline.so: undefined reference to `UP' /usr/local/lib/libreadline.so: undefined reference to `tputs' /usr/local/lib/libreadline.so: undefined reference to `tgoto' /usr/local/lib/libreadline.so: undefined reference to `tgetnum' /usr/local/lib/libreadline.so: undefined reference to `BC' /usr/local/lib/libreadline.so: undefined reference to `tgetstr' What happens if you run ldd /usr/local/lib/libreadline.so ? It looks like you've got either multiple versions of readline installed and you're linking to the wrong one, or something is really wonky with your readline build. A quick google is showing that all of those symbols are in ncurses, which means that you should probably have ncurses in your linker cache or linker runtime path. By the way, if you don't want readline functionality in psql (or care), just build '--without-readline' --Scott
Re: [GENERAL] Configure fails to find readline libraries
On Tue, May 19, 2009 at 7:23 PM, Jeffrey Trimble wrote: > Hi.. thanks. > Here's a printout from my find command: > > /usr/local/include/readline > /usr/local/include/readline/readline.h > > > And the plot thickens. Here is where it errors out and then loops endlessly > until it quits: > (There are symbolic links from libreadline.so to libreadline.so.6.0) > > configure:6240: $? = 0 > configure:6243: test -s conftest > configure:6246: $? = 0 > configure:6316: result: none required > configure:6337: checking for -lreadline > configure:6364: gcc -o conftest -I/usr/local/include > -I/usr/local/include/readline -Wall -Wmissing-prototypes -Wpointer-arith > -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing > -fwrapv -D_GNU_SOURCE -I/usr/local/include -I/usr/local/include/readline > -L/usr/local/lib -L/usr/local/lib conftest.c -lreadline -lcrypt -ldl -lm > >&5 > /usr/local/lib/libreadline.so: undefined reference to `PC' > /usr/local/lib/libreadline.so: undefined reference to `tgetflag' > /usr/local/lib/libreadline.so: undefined reference to `tgetent' > /usr/local/lib/libreadline.so: undefined reference to `UP' > /usr/local/lib/libreadline.so: undefined reference to `tputs' > /usr/local/lib/libreadline.so: undefined reference to `tgoto' > /usr/local/lib/libreadline.so: undefined reference to `tgetnum' > /usr/local/lib/libreadline.so: undefined reference to `BC' > /usr/local/lib/libreadline.so: undefined reference to `tgetstr' > What happens if you run ldd /usr/local/lib/libreadline.so ? It looks like you've got either multiple versions of readline installed and you're linking to the wrong one, or something is really wonky with your readline build. A quick google is showing that all of those symbols are in ncurses, which means that you should probably have ncurses in your linker cache or linker runtime path. By the way, if you don't want readline functionality in psql (or care), just build '--without-readline' --Scott
Re: [GENERAL] Configure fails to find readline libraries
Hi.. thanks. Here's a printout from my find command: /usr/local/include/readline /usr/local/include/readline/readline.h And the plot thickens. Here is where it errors out and then loops endlessly until it quits: (There are symbolic links from libreadline.so to libreadline.so.6.0) configure:6240: $? = 0 configure:6243: test -s conftest configure:6246: $? = 0 configure:6316: result: none required configure:6337: checking for -lreadline configure:6364: gcc -o conftest -I/usr/local/include -I/usr/local/ include/readline -Wall -Wmissing-prototypes -Wpointer-arith -Winline - Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing - fwrapv -D_GNU_SOURCE -I/usr/local/include -I/usr/local/include/ readline -L/usr/local/lib -L/usr/local/lib conftest.c -lreadline - lcrypt -ldl -lm >&5 /usr/local/lib/libreadline.so: undefined reference to `PC' /usr/local/lib/libreadline.so: undefined reference to `tgetflag' /usr/local/lib/libreadline.so: undefined reference to `tgetent' /usr/local/lib/libreadline.so: undefined reference to `UP' /usr/local/lib/libreadline.so: undefined reference to `tputs' /usr/local/lib/libreadline.so: undefined reference to `tgoto' /usr/local/lib/libreadline.so: undefined reference to `tgetnum' /usr/local/lib/libreadline.so: undefined reference to `BC' /usr/local/lib/libreadline.so: undefined reference to `tgetstr' collect2: ld returned 1 exit status configure:6370: $? = 1 configure: failed program was: | /* confdefs.h. */ Jeffrey Trimble System LIbrarian William F. Maag Library Youngstown State University 330.941.2483 (Office) jtrim...@cc.ysu.edu http://www.maag.ysu.edu http://digital.maag.ysu.edu On May 19, 2009, at 7:10 PM, Scott Mead wrote: On Tue, May 19, 2009 at 7:08 PM, Jeffrey Trimble wrote: Thanks, but that didn't work. Here's the complete error result from that: checking for -lreadline... no checking for -ledit... no configure: error: readline library not found If you have readline already installed, see config.log for details on the failure. It is possible the compiler isn't looking in the proper directory. Use --without-readline to disable readline support. Are you *sure* that readline is installed in those directories Have you checked config.log to see the actual error message from the linker? --Scott
Re: [GENERAL] Help with join syntax sought
On May 19, 2009, at 11:29 PM, Andy Colson wrote: I'm not sure what this will do: HAVING COUNT(fxr.currency_code_quote) = 1 The only time I have ever used HAVING is like: select name from something group by name having count(*) > 1 to find duplicate name's. That will leave out all results of multiple rows from the group by, which is not the desired result I think. IIUC the desired result is to leave out duplicates after the first match, not to leave out all results that have duplicates. I think you want something like: HAVING effective_from = MAX(effective_from) Or you ORDER BY effective_from DESC and use DISTINCT ON to ignore the duplicates after the first match (which is the newest currency due to the ordering). I wonder whether it's possible to have effective_from dates in the future though, that would complicate things slightly more... Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4a133d4d10091830814072! -- 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] Configure fails to find readline libraries
On Tue, May 19, 2009 at 7:08 PM, Jeffrey Trimble wrote: > Thanks, but that didn't work. Here's the complete error result from that: > checking for -lreadline... no > checking for -ledit... no > configure: error: readline library not found > If you have readline already installed, see config.log for details on the > failure. It is possible the compiler isn't looking in the proper > directory. > Use --without-readline to disable readline support. > Are you *sure* that readline is installed in those directories Have you checked config.log to see the actual error message from the linker? --Scott
Re: [GENERAL] Configure fails to find readline libraries
Thanks, but that didn't work. Here's the complete error result from that: checking for -lreadline... no checking for -ledit... no configure: error: readline library not found If you have readline already installed, see config.log for details on the failure. It is possible the compiler isn't looking in the proper directory. Use --without-readline to disable readline support. Jeffrey Trimble System LIbrarian William F. Maag Library Youngstown State University 330.941.2483 (Office) jtrim...@cc.ysu.edu http://www.maag.ysu.edu http://digital.maag.ysu.edu On May 19, 2009, at 6:56 PM, Scott Mead wrote: On Tue, May 19, 2009 at 6:34 PM, Jeffrey Trimble wrote: I'm attempting to install 8.2x postgresql, and configure fails to find the realine libraries. Here's my configure: ./configure --with-includes=/usr/local/include:/usr/local/include/ readline --with-libraries=/usr/local/lib Before running configure, try export CFLAGS="-I/usr/local/include -I/usr/local/include/readline" export LDFLAGS="-L/usr/local/lib" --Scott
Re: [GENERAL] Providing an alternative result when there is no result
Merlin Moncure writes: > On Mon, May 18, 2009 at 3:13 PM, Joshua Berry wrote: >> Is there an easy and efficient way to return a boolean false for a query >> that returns no result, and true for one that does return a result? > Probably the best general approach is to: > select count(*) = 1 from > ( >limit 1 > )q; Seems like EXISTS() is much more straightforward ... > the point being that in some cases (not all obviously) the limit 1 can > be a huge win, as you only care if there are rows or not. ... the system does know about optimizing EXISTS as if it were a LIMIT query; you don't need to tack that on yourself. 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] Configure fails to find readline libraries
On Tue, May 19, 2009 at 6:34 PM, Jeffrey Trimble wrote: > I'm attempting to install 8.2x postgresql, and configure fails to find the > realine libraries. > Here's my configure: > > ./configure --with-includes=/usr/local/include:/usr/local/include/readline > --with-libraries=/usr/local/lib > Before running configure, try export CFLAGS="-I/usr/local/include -I/usr/local/include/readline" export LDFLAGS="-L/usr/local/lib" --Scott
[GENERAL] Configure fails to find readline libraries
I'm attempting to install 8.2x postgresql, and configure fails to find the realine libraries. Here's my configure: ./configure --with-includes=/usr/local/include:/usr/local/include/ readline --with-libraries=/usr/local/lib The readline.h file is in the include directory and the lib has the libreadline.so.6, etc. We are using readline 6.0 on Suse Linux 11. (kernel version: 2.6.27.21-0.1-pae) Any ideas why configure is not able to find the readline.h when I am telling where it is? Thanks in advance. --Jeff Jeffrey Trimble System LIbrarian William F. Maag Library Youngstown State University 330.941.2483 (Office) jtrim...@cc.ysu.edu http://www.maag.ysu.edu http://digital.maag.ysu.edu
Re: [GENERAL] Help with join syntax sought
Andy Colson wrote: James B. Byrne wrote: I have a requirement to select the effective exchange rate for a number of currencies as of a specific date and time. The rates may come from several sources for the same currency. For some currencies the rate may be set infrequently. I have come close to getting this to work but cannot seem to get the last bit figured out. Thus my appeal for help. Here is what I have so far: SELECT fxr.currency_code_base AS fx_base, fxr.currency_code_quote AS fx_quote, fxr.effective_from AS fx_date, fxr.currency_exchange_type AS fx_type, fxr.currency_exchange_rate AS fx_rate FROM currency_exchange_rates AS fxr LEFT OUTER JOIN currency_exchange_rates AS fxr_j ON fxr.currency_code_base = fxr_j.currency_code_base AND fxr.currency_code_quote= fxr_j.currency_code_quote AND fxr.currency_exchange_type = fxr_j.currency_exchange_type AND fxr.effective_from >= fxr_j.effective_from WHERE fxr.currency_code_base = 'CAD' AND fxr.effective_from <= current_timestamp GROUP BY fx_base, fxr.currency_code_quote, fx_date, fxr.currency_exchange_type, fx_rate HAVING COUNT(fxr.currency_code_quote) = 1 ORDER BY fx_base, fxr.currency_code_quote, fx_date DESC I see currency_code_base = 'CAD', so you are looking for the most recent Canadian exchange rate. > The rates may > come from several sources for the same currency. What field is the source? currency_code_quote? -Andy Hum... I can quite figure what we are looking for. you say: currencies as of a specific date and time but there is not date stuff in the where... so we'll ignore that. you say: come from several sources but doesnt seem important, we'll ignore that. you say: I want only the most recent regardless of type so type is unimporttant How about this: select * from fxr where code_base = 'CAD' and effective_from = (select max(effective_from) from fxr f2 where f2.code_base=fxr.code_base and f2.code_quote=fxr.code_quote); (forgive the shortened names), it selects any 'CAD' of only the most recent time, based on code_base and code_quote. (had to guess at the fields) Oh! I see one problem.. if the effective_from is exactly the same it could return more records than you want. Have to think about that... -Andy -- 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] Help with join syntax sought
James B. Byrne wrote: I have a requirement to select the effective exchange rate for a number of currencies as of a specific date and time. The rates may come from several sources for the same currency. For some currencies the rate may be set infrequently. I have come close to getting this to work but cannot seem to get the last bit figured out. Thus my appeal for help. Here is what I have so far: SELECT fxr.currency_code_base AS fx_base, fxr.currency_code_quote AS fx_quote, fxr.effective_from AS fx_date, fxr.currency_exchange_type AS fx_type, fxr.currency_exchange_rate AS fx_rate FROM currency_exchange_rates AS fxr LEFT OUTER JOIN currency_exchange_rates AS fxr_j ON fxr.currency_code_base = fxr_j.currency_code_base AND fxr.currency_code_quote= fxr_j.currency_code_quote AND fxr.currency_exchange_type = fxr_j.currency_exchange_type AND fxr.effective_from >= fxr_j.effective_from WHERE fxr.currency_code_base = 'CAD' AND fxr.effective_from <= current_timestamp GROUP BY fx_base, fxr.currency_code_quote, fx_date, fxr.currency_exchange_type, fx_rate HAVING COUNT(fxr.currency_code_quote) = 1 ORDER BY fx_base, fxr.currency_code_quote, fx_date DESC I see currency_code_base = 'CAD', so you are looking for the most recent Canadian exchange rate. > The rates may > come from several sources for the same currency. What field is the source? currency_code_quote? -Andy -- 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] Direct I/O and postgresql version
Pal, Dipali (UMKC-Student) wrote: Hi All, I have one question. Which of the recent versions of postgresql support direct I/O? that would be, none. -- 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] origins/destinations
Andy Colson wrote: Carson Farmer wrote: Hi list, I have (what I thought was) a relatively simple problem, but my knowledge of sql is just not good enough to get this done: I have a table which is basically a number of individuals with both their origin and destination as columns (see Table 1). In this case, origins and destinations are the census area in which they and work. What I would like to do is generate an nxn matrix (preferably output to csv but I'll take what I can get), where origins are on the y axis, and destinations on the x axis (see Table 3). Would it have to be sql only? I think this would be pretty easy in perl. indeed, this would better be done outside the database. you're generating a sparse table of N x N dimensions and likely only relatively few elements populated, unless your population count greatly exceeds the number of locations. I think I'd do a SQL query for distinct(source,dest),count(population) and then use this to fill your matrix on the client side. -- 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] Help with join syntax sought
James B. Byrne wrote: On Tue, May 19, 2009 17:02, Andy Colson wrote: so: select max(name), type from food group by type works cuz we only get one name (the max name) back for each type. or: select name, type from food group by type, name which in our example is kinda pointless, but still, give us the distinct items for "type, name". Thanks. I think I am beginning to see this. So, if there are more than one type for a given currency code and I do not select and group by type then the having count(whatever) = 1 means that these rows are not selected either. Is that correct? I'm not familiar with your data, and I didn't study your sql very hard. I'm not sure what this will do: HAVING COUNT(fxr.currency_code_quote) = 1 The only time I have ever used HAVING is like: select name from something group by name having count(*) > 1 to find duplicate name's. you're group by is on 5 fields, but the count is only on one so... If a count is in the select part (like select count(name) from stuff) it only counts when name is not null... so maybe that having is saying count where currency_code_quote is not null and there is only one record per group... I dunno. -Andy -- 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] Help with join syntax sought
James B. Byrne wrote: On Tue, May 19, 2009 16:41, Andy Colson wrote: If your query above is getting you mostly what you want, just use it as a derived table. I lack the experience to understand what this means. If, as you suggest, I use a subquery as the expression to the main SELECT and for it I use the syntax that returns every distinct combination of base, quote, timestamp, and type, then what? Unless I am missing something then I still have too many rows for those currencies with more than one type. I was not offering a specific answer, just another tool you could use to massage the data more. What I'm saying is, with that first query you got the data into a new format Think of that result set as a table itself. As if you had done: create table tmp (lots of field); insert into tmp select (that big query you first posted) now can you select from tmp to get your answer? Once again, I may be leading you down the wrong path, and as I said before I don't know your data/layouts, and I didn't study your sql very much. -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Direct I/O and postgresql version
Hi All, I have one question. Which of the recent versions of postgresql support direct I/O? Thanks, Dipali Pal -- 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] Help with join syntax sought
On Tue, May 19, 2009 17:02, Andy Colson wrote: > > so: select max(name), type from food group by type > works cuz we only get one name (the max name) back for each type. > > or: select name, type from food group by type, name > which in our example is kinda pointless, but still, give us the > distinct > items for "type, name". Thanks. I think I am beginning to see this. So, if there are more than one type for a given currency code and I do not select and group by type then the having count(whatever) = 1 means that these rows are not selected either. Is that correct? -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte & Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Help with join syntax sought
On Tue, May 19, 2009 16:41, Andy Colson wrote: > If your query above is getting you mostly what you want, just use it > as a derived table. > I lack the experience to understand what this means. If, as you suggest, I use a subquery as the expression to the main SELECT and for it I use the syntax that returns every distinct combination of base, quote, timestamp, and type, then what? Unless I am missing something then I still have too many rows for those currencies with more than one type. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte & Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Help with join syntax sought
James B. Byrne wrote: I have a requirement to select the effective exchange rate for a number of currencies as of a specific date and time. The rates may come from several sources for the same currency. For some currencies the rate may be set infrequently. I have come close to getting this to work but cannot seem to get the last bit figured out. Thus my appeal for help. The table currency_exchange_rates has a composite unique index made up of: fxr.currency_code_base fxr.currency_code_quote fxr.effective_from fxr.currency_exchange_type Here is what I have so far: SELECT fxr.currency_code_base AS fx_base, fxr.currency_code_quote AS fx_quote, fxr.effective_from AS fx_date, fxr.currency_exchange_type AS fx_type, fxr.currency_exchange_rate AS fx_rate FROM currency_exchange_rates AS fxr LEFT OUTER JOIN currency_exchange_rates AS fxr_j ON fxr.currency_code_base = fxr_j.currency_code_base AND fxr.currency_code_quote= fxr_j.currency_code_quote AND fxr.currency_exchange_type = fxr_j.currency_exchange_type AND fxr.effective_from >= fxr_j.effective_from WHERE fxr.currency_code_base = 'CAD' AND fxr.effective_from <= current_timestamp GROUP BY fx_base, fxr.currency_code_quote, fx_date, fxr.currency_exchange_type, fx_rate HAVING COUNT(fxr.currency_code_quote) = 1 ORDER BY fx_base, fxr.currency_code_quote, fx_date DESC My problem with this version is that currencies with rates from more than one type show up for each type. This I do not want. I want only the most recent regardless of type. However, I need to display the type in the final report. Further, if I take the fxr.currency_exchange_type out of the SELECT columns, making the appropriate adjustments elsewhere, then all those currencies with more than one type are not selected at all. I am perplexed why I cannot select a column from the table without having to include it in the GROUP BY clause as well. Any help is welcomed. If your query above is getting you mostly what you want, just use it as a derived table. so: select subtable.fx_base, etc from ( > SELECT > fxr.currency_code_base AS fx_base, > fxr.currency_code_quote AS fx_quote, > fxr.effective_from AS fx_date, > fxr.currency_exchange_type AS fx_type, > fxr.currency_exchange_rate AS fx_rate > > FROM > currency_exchange_rates AS fxr > > LEFT OUTER JOIN > currency_exchange_rates AS fxr_j > > ON > fxr.currency_code_base = fxr_j.currency_code_base > AND > fxr.currency_code_quote= fxr_j.currency_code_quote > AND > fxr.currency_exchange_type = fxr_j.currency_exchange_type > AND > fxr.effective_from >= fxr_j.effective_from > > WHERE > fxr.currency_code_base = 'CAD' > AND > fxr.effective_from <= current_timestamp > > GROUP BY > fx_base, > fxr.currency_code_quote, > fx_date, > fxr.currency_exchange_type, > fx_rate > > HAVING > COUNT(fxr.currency_code_quote) = 1 > > ORDER BY > fx_base, > fxr.currency_code_quote, > fx_date DESC ) as subtable -- 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] Help with join syntax sought
James B. Byrne wrote: I am perplexed why I cannot select a column from the table without having to include it in the GROUP BY clause as well. Any help is welcomed. Group by is saying "I want only one row returned for each distinct value in this column" so a food table like this: name | type -- apple | fruit pie | desert orange| fruit if you: select name, type from food group by type your saying, give me only one row for each "type"... but there are two records where type = 'fruit', so how do you return two values (apple, orange) in only one row? That's why all fields in the select list must be an aggregate function, or in the group by list. so: select max(name), type from food group by type works cuz we only get one name (the max name) back for each type. or: select name, type from food group by type, name which in our example is kinda pointless, but still, give us the distinct items for "type, name". -Andy -- 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] origins/destinations
Carson Farmer wrote: Hi list, I have (what I thought was) a relatively simple problem, but my knowledge of sql is just not good enough to get this done: I have a table which is basically a number of individuals with both their origin and destination as columns (see Table 1). In this case, origins and destinations are the census area in which they and work. What I would like to do is generate an nxn matrix (preferably output to csv but I'll take what I can get), where origins are on the y axis, and destinations on the x axis (see Table 3). Would it have to be sql only? I think this would be pretty easy in perl. -Andy -- 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] origins/destinations
Andy Colson wrote:
Carson Farmer wrote:
Hi list,
I have (what I thought was) a relatively simple problem, but my
knowledge of sql is just not good enough to get this done:
I have a table which is basically a number of individuals with both
their origin and destination as columns (see Table 1). In this case,
origins and destinations are the census area in which they and work.
What I would like to do is generate an nxn matrix (preferably output
to csv but I'll take what I can get), where origins are on the y axis,
and destinations on the x axis (see Table 3).
Would it have to be sql only? I think this would be pretty easy in perl.
-Andy
I took the liberty of assuming the origins and destinations could have
different values
Something like this:
#!/usr/bin/perl -w
use strict;
use DBI;
my $sql = 'select origin, dest, count(*) from tmp group by origin, dest';
my $db = DBI->connect('dbi:Pg:dbname=andy', 'andy', '') or die;
my $orlist = $db->selectcol_arrayref('select distinct origin from tmp
order by origin');
my $dstlist = $db->selectcol_arrayref('select distinct dest from tmp
order by dest');
my %table;
my $q = $db->prepare($sql);
$q->execute();
while (my($origin, $dest, $cc) = $q->fetchrow_array)
{
$table{$origin}->{$dest} += $cc;
}
print "origins\t";
foreach my $dst (@$dstlist)
{
print "$dst\t";
}
print "\n";
foreach my $ori (@$orlist)
{
print "$ori\t";
foreach my $dst (@$dstlist)
{
my $v = $table{$ori}->{$dst};
if (! $v) {
$v = '0';
}
print "$v\t";
}
print "\n";
}
--
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] Locking to restrict rowcounts.
Richard Huxton wrote: Glen Parker wrote: Richard Huxton wrote: #3 won't work unless the other transactions have all committed by the time you do the check. It is guaranteed to fail at some point. If it's in an AFTER INSERT/UPDATE trigger then whatever transaction takes you beyond 10 rows you will always get a failure. If you get a lot of insert/delete pairings then you could spend a lot of time counting rows, but that's all. I still don't think it will work. Two concurrent transactions could still screw the data up. Before: 9 rows. Tx 1: See 9 rows, issue insert, see 10 rows. Tx 2: See 9 rows, issue insert, see 10 rows. After: 11 rows. -Glen -- 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] Locking to restrict rowcounts.
Richard Huxton wrote:
Shakil Shaikh wrote:
Hi,
Consider the following scenario:
CREATE FUNCTION test(name)
select into cnt count(id) from items where owner = name;
--suppose someone inserts during this point? then next check will
succeed when it should not.
if (cnt < 10) then
insert into items values ('new item', name);
end;
end;
What is the best way to stop this function from entering too many
items in a concurrent context? I think a lock is needed, although I'm
not sure which would be most appropriate since the count requires the
whole table
Well, you can't use FOR UPDATE with aggregates, otherwise you could do:
SELECT into cnt count(id) FROM items WHERE owner = name FOR UPDATE;
So, you'd need:
PERFORM * FROM items WHERE owner = name FOR UPDATE;
That won't stop someone blindly inserting into items, but it will act as
an effective lock on calling test() concurrently.
The other options would be:
1. Lock the relevant row in the users table (not so good if you have an
items2 table with similar requirements)
2. Advisory locks at the application level
3. Check after an insert on the items table and raise an exception if
there are 11+ items.
I'd be tempted by #3 - assuming most of the time you won't breach this
limit.
#3 won't work unless the other transactions have all committed by the
time you do the check. It is guaranteed to fail at some point.
In cases like this, in the unusual case that I don't already have some
suitable row locked, I just pick a row in some table that makes sense to
lock. There would be nothing wrong with creating a table with rows that
exist solely for the purpose of locking. This is a (usually) better
version of option #2 above.
-Glen
--
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] Locking to restrict rowcounts.
Glen Parker wrote: Richard Huxton wrote: 3. Check after an insert on the items table and raise an exception if there are 11+ items. I'd be tempted by #3 - assuming most of the time you won't breach this limit. #3 won't work unless the other transactions have all committed by the time you do the check. It is guaranteed to fail at some point. If it's in an AFTER INSERT/UPDATE trigger then whatever transaction takes you beyond 10 rows you will always get a failure. If you get a lot of insert/delete pairings then you could spend a lot of time counting rows, but that's all. > There would be nothing wrong with creating a table with rows that > exist solely for the purpose of locking. This is a (usually) better > version of option #2 above. Of course, if you're going to have a separate table then you might as well store the count in there and actually update it on every insert/update/delete. Assuming you might find the count of some use somewhere. Set the fill-factor for the lock table and HOT should prevent the table bloating too. -- Richard Huxton Archonet Ltd -- 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] Error while including PQXX library
On Tue, May 19, 2009 at 12:37, aravind chandu wrote: > Hello, > > I wrote a small program in cpp which uses pqxx API.The file that i > included are > > #include > #include > > After compiling i got the following error : > prog29.cpp:5:21: pqxx/connection: No such file or directory > prog29.cpp:5:21: pqxx/transaction: No such file or directory > > I checked the include directory and all these files were there,but i > don't know why i got these errors.Can you guys please help me to solve this > problem. Random guess but check that your include path points to the directory containing the pqxx director, for example if you're connection and transaction header files are in some/path/include/pqxx make sure you add to the compiler some/path/include not some/path/include/pqxx > > Thanks, > Aravind. > > > > -- 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] Error while including PQXX library
aravind chandu wrote: #include #include prog29.cpp:5:21: pqxx/connection: No such file or directory prog29.cpp:5:21: pqxx/transaction: No such file or directory I checked the include directory and all these files were there Doubtful. I've never heard of a header file without an extension. I'm guessing that: 1. You are on Windows 2. You have explorer set to "hide known extensions" (it's in folder options) Try pqxx/connection.h or whatever the extension is once you've got explorer to display them. -- Richard Huxton Archonet Ltd -- 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] Locking to restrict rowcounts.
Shakil Shaikh wrote:
Hi,
Consider the following scenario:
CREATE FUNCTION test(name)
select into cnt count(id) from items where owner = name;
--suppose someone inserts during this point? then next check will
succeed when it should not.
if (cnt < 10) then
insert into items values ('new item', name);
end;
end;
What is the best way to stop this function from entering too many items
in a concurrent context? I think a lock is needed, although I'm not sure
which would be most appropriate since the count requires the whole table
Well, you can't use FOR UPDATE with aggregates, otherwise you could do:
SELECT into cnt count(id) FROM items WHERE owner = name FOR UPDATE;
So, you'd need:
PERFORM * FROM items WHERE owner = name FOR UPDATE;
That won't stop someone blindly inserting into items, but it will act as
an effective lock on calling test() concurrently.
The other options would be:
1. Lock the relevant row in the users table (not so good if you have an
items2 table with similar requirements)
2. Advisory locks at the application level
3. Check after an insert on the items table and raise an exception if
there are 11+ items.
I'd be tempted by #3 - assuming most of the time you won't breach this
limit.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Help with join syntax sought
I have a requirement to select the effective exchange rate for a number of currencies as of a specific date and time. The rates may come from several sources for the same currency. For some currencies the rate may be set infrequently. I have come close to getting this to work but cannot seem to get the last bit figured out. Thus my appeal for help. The table currency_exchange_rates has a composite unique index made up of: fxr.currency_code_base fxr.currency_code_quote fxr.effective_from fxr.currency_exchange_type Here is what I have so far: SELECT fxr.currency_code_base AS fx_base, fxr.currency_code_quote AS fx_quote, fxr.effective_from AS fx_date, fxr.currency_exchange_type AS fx_type, fxr.currency_exchange_rate AS fx_rate FROM currency_exchange_rates AS fxr LEFT OUTER JOIN currency_exchange_rates AS fxr_j ON fxr.currency_code_base = fxr_j.currency_code_base AND fxr.currency_code_quote= fxr_j.currency_code_quote AND fxr.currency_exchange_type = fxr_j.currency_exchange_type AND fxr.effective_from >= fxr_j.effective_from WHERE fxr.currency_code_base = 'CAD' AND fxr.effective_from <= current_timestamp GROUP BY fx_base, fxr.currency_code_quote, fx_date, fxr.currency_exchange_type, fx_rate HAVING COUNT(fxr.currency_code_quote) = 1 ORDER BY fx_base, fxr.currency_code_quote, fx_date DESC My problem with this version is that currencies with rates from more than one type show up for each type. This I do not want. I want only the most recent regardless of type. However, I need to display the type in the final report. Further, if I take the fxr.currency_exchange_type out of the SELECT columns, making the appropriate adjustments elsewhere, then all those currencies with more than one type are not selected at all. I am perplexed why I cannot select a column from the table without having to include it in the GROUP BY clause as well. Any help is welcomed. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte & Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Locking to restrict rowcounts.
Hi,
Consider the following scenario:
CREATE FUNCTION test(name)
select into cnt count(id) from items where owner = name;
--suppose someone inserts during this point? then next check will succeed
when it should not.
if (cnt < 10) then
insert into items values ('new item', name);
end;
end;
What is the best way to stop this function from entering too many items in a
concurrent context? I think a lock is needed, although I'm not sure which
would be most appropriate since the count requires the whole table (or at
least no adds to have occurred I think I read something about predicate
locking which sounds relevant but isn't supported in PostgreSQL. Ideally I
want some kind of lock only relevant to "name" above.
Any strategies to deal with this?
Shak
--
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] Providing an alternative result when there is no result
On Mon, May 18, 2009 at 3:13 PM, Joshua Berry wrote: > Hello all, > > Is there an easy and efficient way to return a boolean false for a query > that returns no result, and true for one that does return a result? > Probably the best general approach is to: select count(*) = 1 from ( limit 1 )q; the point being that in some cases (not all obviously) the limit 1 can be a huge win, as you only care if there are rows or not. with little work (you have to be aware of if/when you can tack 'limit 1 onto a query) you could generalize it into a pl/pgsql dynamic sql function taking a query string. merlin -- 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] Get block of N numbers from sequence
On Tue, May 19, 2009 at 9:32 AM, Thomas Guettler wrote:
>
>
> hubert depesz lubaczewski schrieb:
>> On Tue, May 19, 2009 at 01:45:17PM +0200, Thomas Guettler wrote:
>>> how can you get N numbers (without holes) from a sequence?
>>
>> alter sequence XXX increment by 1000;
>> select nextval('XXX');
>> alter sequence XXX increment by 1;
>
> If other processes run nextval() between "increment by 1000" and "increment
> by 1",
> they leave big holes in the sequence.
This is only works if everyone does it this way. If anybody throws a
nextval() without locking the sequence first you have a race. Also,
since alter sequence takes a full lock your concurrency is zero.
Probably the best general way to attack this problem is using advisory
locks. note the code below is untested.
create or replace function my_nextval(_seq text, _count int, _v out
bigint) returns bigint as
$$
begin
if _count = 1 then
perform pg_advisory_lock_shared(999);
_v := nextval(_seq);
perform pg_advisory_unlock_shared(999);
else
perform pg_advisory_lock(999);
_v := nextval(_seq);
perform setval(_seq, _v + _count);
perform pg_advisory_unlock(999);
end if;
end;
$$ language plpgsql;
This complexity is worthwhile if you need to intermix standard
nextval() with block allocations. the number 999 has no significance
except as a lock id...see the docs on advisory locks. The advantage
here is that nextval() calls do not block each other and the full lock
is extremely short term, so you can safely call this during longer
running transaction.
merlin
--
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] origins/destinations
Carson Farmer wrote: Hi list, I have (what I thought was) a relatively simple problem, but my knowledge of sql is just not good enough to get this done: I have a table which is basically a number of individuals with both their origin and destination as columns (see Table 1). In this case, origins and destinations are the census area in which they and work. What I would like to do is generate an nxn matrix (preferably output to csv but I'll take what I can get), where origins are on the y axis, and destinations on the x axis (see Table 3). Google a little for crosstab queries with the tablefunc add-ons in the contrib/ directory. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] origins/destinations
Hi list, I have (what I thought was) a relatively simple problem, but my knowledge of sql is just not good enough to get this done: I have a table which is basically a number of individuals with both their origin and destination as columns (see Table 1). In this case, origins and destinations are the census area in which they and work. What I would like to do is generate an nxn matrix (preferably output to csv but I'll take what I can get), where origins are on the y axis, and destinations on the x axis (see Table 3). I can already group by both origins and destinations to produce Table 2, but I don't know what steps are needed to get to Table 3. Any help or suggestions are greatly appreciated! Table 1 id | origin | destination 1area1 area5 2area1 area5 3area1 area5 4area2 area4 5area4 area2 6area5 area5 7area2 area4 8area2 area4 9area4 area3 10 area3 area5 ... Table 2 id | origin | destination | count 1area1 area53 4area2 area43 5area4 area21 6area5 area51 9area4 area31 10 area3 area51 ... Table 3 origins | area1 | area2 | area3 | area4 | area5 | ... area10 0 0 0 3 area20 0 0 3 0 area30 0 0 0 1 area40 1 1 0 0 area50 0 0 0 1 ... Regards, Carson -- Carson J. Q. Farmer ISSP Doctoral Fellow National Centre for Geocomputation (NCG), Email: carson.far...@gmail.com Web: http://www.carsonfarmer.com/ http://www.ftools.ca/ -- 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] Get block of N numbers from sequence
Thomas Guettler írta:
> Hi,
>
> how can you get N numbers (without holes) from a sequence?
>
> Thomas
>
>
# create sequence tmp_seq cache 1000;
CREATE SEQUENCE
>From the same client:
# select nextval('tmp_seq');
nextval
-
1
(1 sor)
# select nextval('tmp_seq');
nextval
-
2
(1 sor)
# select nextval('tmp_seq');
nextval
-
3
(1 sor)
# select nextval('tmp_seq');
nextval
-
4
(1 sor)
... (ad nauseum)
>From another psql client:
# select nextval('tmp_seq');
nextval
-
1001
(1 sor)
# select nextval('tmp_seq');
nextval
-
1002
(1 sor)
# select nextval('tmp_seq');
nextval
-
1003
(1 sor)
# select nextval('tmp_seq');
nextval
-
1004
(1 sor)
# select nextval('tmp_seq');
nextval
-
1005
(1 sor)
...
You can get up to 1000 (or the number specified as CACHE N)
numbers in a series (without holes) in he same client at once,
you don't even need to be in the same transaction.
Best regards,
Zoltán Böszörményi
--
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics
--
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Error while including PQXX library
Hello, I wrote a small program in cpp which uses pqxx API.The file that i included are #include #include After compiling i got the following error : prog29.cpp:5:21: pqxx/connection: No such file or directory prog29.cpp:5:21: pqxx/transaction: No such file or directory I checked the include directory and all these files were there,but i don't know why i got these errors.Can you guys please help me to solve this problem. Thanks, Aravind.
Re: [GENERAL] INTERVAL data type and libpq - what format?
Tom Lane wrote: Sam Mason writes: I don't really know 8.4, but I believe you're saying here that you explicitly want the values to be of basic INTERVAL type here, i.e. not INTERVAL DAY TO HOUR for parameter 3. Right, you can get the equivalent behavior from psql thus: regression=# select '-12345'::interval::interval year; interval -- 00:00:00 (1 row) regression=# select '12 11'::interval::interval year; ERROR: invalid input syntax for type interval: "12 11" LINE 1: select '12 11'::interval::interval year; ^ There is not any way to bind a more specific type to a parameter at the protocol level. I think PG may do the right thing if you don't specify the types when preparing the query, but haven't tested. Yeah, that should work (though I haven't verified it either). Another common trick is to specify the type in the text of the query by casting the parameter symbol: PQprepare( ... $2::INTERVAL YEAR ... ); I'd say this is better style than hard-wiring numeric type OIDs into your code. Remember we are implementing a database driver with equivalent features and an ODBC driver for PostgreSQL, executing queries with ? parameter placeholders in the SQL text... Since SQL Parameter types are not known at (4gl language-level) PREPARE time, we wait for the (4gl) EXECUTE time to do the real PQprepare() with paramTypes[]... (this is a pity by the way since we can't get any SQL error at PREPARE time). It's not that easy for us to add the :: clauses because the conversion of the ? placeholders to $n is done at PREPARE time, when types are not yet yet... so this means major rewriting... But this is all internal stuff you are not interested in, the main question I would like to ask is: What versions of PostgreSQL are 100% sure supporting the $n:: clauses? We have to support all PostgreSQL versions, starting from 8.0 ... Thanks Seb -- 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] Providing an alternative result when there is no result
On Mon, May 18, 2009 at 03:13:56PM -0400, Joshua Berry wrote: > Hello all, > > Is there an easy and efficient way to return a boolean false for a query > that returns no result, and true for one that does return a result? > > Currently we select the result into a temp table. > > SELECT INTO temp_table id FROM ... ; > IF temp_table IS NULL THEN > resp:= 'NO'; > ELSE > resp:= 'YES'; > END IF; SELECT EXISTS (SELECT 1 FROM ); will get you a boolean which can't be NULL. You can either map that to "yes/no" or return it as is. Hope this helps :) Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com 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] array/function question
2009/5/19 Alvaro Herrera : > Pavel Stehule escribió: > >> postgres=# create or replace function xx(anyarray, anyarray) returns >> bool[] as $$ >> select array(select (select x = any(select y from unnest($2) g2(y))) >> from unnest($1) g(x)) >> $$ language sql immutable; >> CREATE FUNCTION > > There ain't no unnest() function in 8.3 ... I am sorry create or replace function unnest(anyarray) returns setof anyelement as $$ select $1[i] from generate_series(array_lower($1,1), array_upper($1,1)) g(i) $$ language sql immutable; when I looked on my code, it could be simplified >> postgres=# create or replace function xx(anyarray, anyarray) returns >> bool[] as $$ >> select array(select (select x = any($2))) >> from unnest($1) g(x)) >> $$ language sql immutable; regards Pavel Stehule > > -- > Alvaro Herrera http://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support > -- 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] Get block of N numbers from sequence
On Tue, May 19, 2009 at 03:32:16PM +0200, Thomas Guettler wrote:
> If other processes run nextval() between "increment by 1000" and "increment
> by 1",
> they leave big holes in the sequence.
yes. I know. there is no other way.
if you want 1000 ids, but they don't have to be consequtive, you can
simply:
select nextval('qq') from generate_series(1,1000);
depesz
--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
--
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] INTERVAL data type and libpq - what format?
Sam Mason writes: > I don't really know 8.4, but I believe you're saying here that you > explicitly want the values to be of basic INTERVAL type here, i.e. not > INTERVAL DAY TO HOUR for parameter 3. Right, you can get the equivalent behavior from psql thus: regression=# select '-12345'::interval::interval year; interval -- 00:00:00 (1 row) regression=# select '12 11'::interval::interval year; ERROR: invalid input syntax for type interval: "12 11" LINE 1: select '12 11'::interval::interval year; ^ There is not any way to bind a more specific type to a parameter at the protocol level. > I think PG may do the right thing if you > don't specify the types when preparing the query, but haven't tested. Yeah, that should work (though I haven't verified it either). Another common trick is to specify the type in the text of the query by casting the parameter symbol: PQprepare( ... $2::INTERVAL YEAR ... ); I'd say this is better style than hard-wiring numeric type OIDs into your code. 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] array/function question
Pavel Stehule escribió: > postgres=# create or replace function xx(anyarray, anyarray) returns > bool[] as $$ > select array(select (select x = any(select y from unnest($2) g2(y))) > from unnest($1) g(x)) > $$ language sql immutable; > CREATE FUNCTION There ain't no unnest() function in 8.3 ... -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] array/function question
Joshua Berry escribió: > Please forgive the lack of grace. I'd love tips on how to improve this! Tip: follow Pavel's suggestion. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] Get block of N numbers from sequence
On Tue, May 19, 2009 at 01:45:17PM +0200, Thomas Guettler wrote:
> how can you get N numbers (without holes) from a sequence?
alter sequence XXX increment by 1000;
select nextval('XXX');
alter sequence XXX increment by 1;
Best regards,
depesz
--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
--
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] INTERVAL data type and libpq - what format?
Thank you guys for your input, I really appreciate.
It's a while I haven't posted on this list and be happy to get fast and
accurate answers...
As I wrote in a previous mail, I maintain a database driver for our 4GL runtime
system, allowing Informix 4gl applications to use PostgreSQL.
In this context, we have unknown SQL statements to prepare/execute, so it's
note easy to patch the SQL on the fly to add some cast clauses as Sam
suggested...
Regarding your suggestion to use libpqtypes.h:
As a dev tool provider, we cannot force our customers to rely on add-ons
or extensions. Our driver must work with a standard PostgreSQL database.
By the way,
I would also feel more comfortable if the type ids to be passed to the
paramTypes[] array would be provided in a public header file.
I don't understand why this is not published...
Many thanks,
Seb
Merlin Moncure wrote:
On Tue, May 19, 2009 at 8:17 AM, Sebastien FLAESCH wrote:
Yes, good point.
I realize now that I would have expected libpq to give me a way to specify
the exact decoration or precision of INTERVAL parameters...
As you can do with ODBC's SQLBindParameter(), where you specify the C type,
SQL type, precision/scale or length ...
I believe this is important when it comes to data type conversion (for ex,
when you want to insert a numeric/date/time into a char or the other way).
=> sort of cast, actually...
I known libpq functions like PQexecParams() or PQexecPrepared() have a
paramFormats[] parameter to specify if the buffer will hold a string
or the binary representation of the value... but that would not help
much (I don't want to deal with internal structures!).
You might want to take a look at 'libpqtypes'. It exposes the
internal formats binary formats in easy to use structures.
e.g. (in libpqtypes.h)
typedef struct
{
int years;
int mons;
int days;
int hours;
int mins;
int secs;
int usecs;
} PGinterval;
I was curious, and decided to see what happens when you inserted an
interval with the following code snippet:
PGinterval i;
memset(&i, 0, sizeof(i));
i.secs = 100;
PQputf(p, "%interval", &i);
PQparamExec(c, p, "insert into foo values ($1)", 0);
select * from foo;
i
---
277:46:40
also, libpqtypes always sends in binary which is much faster for the
date/time types.
http://libpqtypes.esilo.com/
merlin
--
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] array/function question
you should use something similar to 'merge sort'
but only if your input is sorted (m_bx expects this)
In my case, order is not guaranteed, and as the result needs to match
the order of the input, it seems that using some exhaustive tail
recursive method is the way to go. (By that I mean a loop within a
loop, testing up to m*n times where m and n are the length of the
arrays passed in.
if your subjects (numbers) are not going beyond a certain limit
eg(65535)
take up an array and filter
For my application, there will likely be no more than 20 elements in
the array, so practical limits are not a problem.
you can generate a poly for array B's roots, and calculate A's points
-where it's 0, then the B array have the value ;)))
writing the function in C is not so easy but it will be fast ;)
Can anyone point me to documentation on the performance differences
between plpgsql/plc/plperl/etc? I googled but only found a few
offhanded comments from mailing list archives and online message
boards. Are there any general guidelines on when it's a good idea to
switch to a language other than plsql or plpsql?
Here's my modified version of Nagy's function. This one allows
unsorted array elements, ordering the tests by the order of the
elements in the first array parameter.
Please forgive the lack of grace. I'd love tips on how to improve
this! In particular, is there a better way to find the length of an
array without having to piecewise handle the empty array case?
create or replace function m_bx(a integer[],b integer[])
returns boolean[]
AS
$BODY$
declare res boolean[];
declare i integer;
declare j integer;
declare la integer;
declare lb integer;
begin
i=1;
j=1;
-- array_upper returns NULL if the length of the array is 0, the
following hacks provided the desired result for empty array cases
-- la=array_upper(a,1);
la = (select CASE WHEN count is null THEN 0 ELSE count END from
(select array_upper(a::int[], 1) as count) as foo);
-- lb=array_upper(b,1);
lb = (select CASE WHEN count is null THEN 0 ELSE count END from
(select array_upper(b::int[], 1) as count) as foo);
loop
if i>la then
exit;
end if;
if (j>lb) then
res[i]=false;
j=1;
i=i+1;
else
if (a[i] = b[j]) then
--b contains this element, move to the next
res[i]=true;
j=1;
i=i+1;
else
j=j+1;
end if;
end if;
end loop;
return res;
end;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE
COST 100;
--Test cases to handle:
select m_bx('{1,2,5,4}','{5, 1, 4}'); --{t,f,t,t}
select m_bx('{1,2,5,4}','{5}'); --{f,f,t,f}
select m_bx('{1,2,5,4}','{}'); --{f,f,f,f}
select m_bx('{}'::int[],'{}'); --{}::bool
Regards,
Joshua Berry
On May 18, 2009, at 10:00 PM, Nagy Zoltan wrote:
create or replace function m_bx(a integer[],b integer[])
returns boolean[]
as
$BODY$
declare res boolean[];
declare i integer;
declare j integer;
declare la integer;
declare lb integer;
begin
i=1;
j=1;
la=array_upper(a,1);
lb=array_upper(b,1);
loop
if i>la then
exit;
end if;
if (j<=lb and a[i] = b[j]) then
res[i]=true;
else
res[i]=false;
end if;
if(b[j]
Hello All,
I'm trying to optimize a few slow queries and helper functions, and
have
found a poor performing function. To improve performance, I'd like to
create a function that does the following:
Inputs:
A: an array of integers. for example: { 1, 2, 3, 4, 7 }
B: an array of integers. for example: { 1, 4, 8, 9 }
Returns
C: an array of bools the same dimensions as Array A. In this
example: {
true, false, false, false, true, false }
Effectively, this function would use Array A as a set of boolean
tests
to exercise on Array B. The result array will have the save number of
elements as array A.
What I lack is the knowledge of how to
1. index and compare arrays when their input size is not known. (I
only
know how to use hardcoded indexes like A[1], B[2], etc.
2. To use control structures for recursion/looping. I've read
http://www.postgresql.org/docs/8.3/interactive/plpgsql-control-structures.html
but
still not sure how to apply the grammar to arrays data types.
If there is a builtin array function that achieves this, that would
be
good to know as well.
Cheers,
-Joshua
Joshua Berry
Re: [GENERAL] Daylight saving time question
- Original Message - From: "Tom Lane" To: "Bayless Kirtley" Cc: "John R Pierce" ; "PostgreSQL" ; Sent: Monday, May 18, 2009 3:22 PM Subject: Re: [GENERAL] Daylight saving time question "Bayless Kirtley" writes: Is this a flaw in the JDBC driver or is that the expected behavior? You'd be more likely to get the correct answer on pgsql-jdbc. regards, tom lane Tom, I'm using org.postgresql.Driver from the jar file postgresql-8.3-603.jdbc3.jar that came with my distribution. Is that not the correct one? Bayless -- 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] INTERVAL data type and libpq - what format?
On Tue, May 19, 2009 at 10:12 AM, Sam Mason wrote: > On Tue, May 19, 2009 at 02:17:20PM +0200, Sebastien FLAESCH wrote: >> As you can do with ODBC's SQLBindParameter(), where you specify the C type, >> SQL type, precision/scale or length ... >> I believe this is important when it comes to data type conversion (for ex, >> when you want to insert a numeric/date/time into a char or the other way). >> => sort of cast, actually... > > Tom sent a message, but it seems to have got lost somewhere. The > suggestion was to leave the paramTypes empty and just write the prepared > statement as: > > INSERT INTO tbl (k,i1,i2) VALUES ($1::INT,$2::INTERVAL YEAR,$3::INTERVAL); > > or similar. That way PG will be able to infer that $1 will be a literal > of integer type, $2 will be of INTERVAL YEAR and so on. In fact for > queries such as this I don't think you even need to put those casts in > there as PG will be able to figure out what you mean automatically (i.e. > it does a limited form of type inference). > >> I known libpq functions like PQexecParams() or PQexecPrepared() have a >> paramFormats[] parameter to specify if the buffer will hold a string >> or the binary representation of the value... but that would not help >> much (I don't want to deal with internal structures!). > > Yes, stay away from binary types if at all possible! > For the record, I disagree with this sentiment strongly. I would rather see the client side library be buffed up so you have an opportunity to deal with the data structures the way the server sees them. The more complex the type is (like date time types), the bigger the win both in terms of performance and feature exposure. I understand though that cross database support is impossible though. merlin -- 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] INTERVAL data type and libpq - what format?
On Tue, May 19, 2009 at 8:17 AM, Sebastien FLAESCH wrote:
> Yes, good point.
>
> I realize now that I would have expected libpq to give me a way to specify
> the exact decoration or precision of INTERVAL parameters...
>
> As you can do with ODBC's SQLBindParameter(), where you specify the C type,
> SQL type, precision/scale or length ...
> I believe this is important when it comes to data type conversion (for ex,
> when you want to insert a numeric/date/time into a char or the other way).
> => sort of cast, actually...
>
> I known libpq functions like PQexecParams() or PQexecPrepared() have a
> paramFormats[] parameter to specify if the buffer will hold a string
> or the binary representation of the value... but that would not help
> much (I don't want to deal with internal structures!).
You might want to take a look at 'libpqtypes'. It exposes the
internal formats binary formats in easy to use structures.
e.g. (in libpqtypes.h)
typedef struct
{
int years;
int mons;
int days;
int hours;
int mins;
int secs;
int usecs;
} PGinterval;
I was curious, and decided to see what happens when you inserted an
interval with the following code snippet:
PGinterval i;
memset(&i, 0, sizeof(i));
i.secs = 100;
PQputf(p, "%interval", &i);
PQparamExec(c, p, "insert into foo values ($1)", 0);
select * from foo;
i
---
277:46:40
also, libpqtypes always sends in binary which is much faster for the
date/time types.
http://libpqtypes.esilo.com/
merlin
--
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] my insertion script don't work
Solved...mine error...the script was create under windows and launched under linux...the file format was not corrent... Thanks :-) kelvin273 wrote: > > Hi all, > i'm using postgresql 8.3 and i have create a very simple script for insert > data into database (because i'm testing it, i create and drop/mod > frequently the tables). > My script is this: > > #!/bin/bash > echo "Insert start" > psql -U myself -d test -f 000_ins_users.sql > psql -U myself -d test -f 001_ins_profile.sql > psql -U myself -d test -f 002_ins_user_prof.sql > echo "Insert end" > > but i receive this error when i launch this: > > Insert start > : Invalid argument > : Invalid argumentl > : Invalid argumentsql > Insert end > > It looks like that the filename argument is not ok...and the error is > different because the filename (length of that) is different...can you > help me, please? > Thank you very much! > > > -- View this message in context: http://www.nabble.com/my-insertion-script-don%27t-work-tp23611570p23613254.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INTERVAL data type and libpq - what format?
On Tue, May 19, 2009 at 02:17:20PM +0200, Sebastien FLAESCH wrote: > As you can do with ODBC's SQLBindParameter(), where you specify the C type, > SQL type, precision/scale or length ... > I believe this is important when it comes to data type conversion (for ex, > when you want to insert a numeric/date/time into a char or the other way). > => sort of cast, actually... Tom sent a message, but it seems to have got lost somewhere. The suggestion was to leave the paramTypes empty and just write the prepared statement as: INSERT INTO tbl (k,i1,i2) VALUES ($1::INT,$2::INTERVAL YEAR,$3::INTERVAL); or similar. That way PG will be able to infer that $1 will be a literal of integer type, $2 will be of INTERVAL YEAR and so on. In fact for queries such as this I don't think you even need to put those casts in there as PG will be able to figure out what you mean automatically (i.e. it does a limited form of type inference). > I known libpq functions like PQexecParams() or PQexecPrepared() have a > paramFormats[] parameter to specify if the buffer will hold a string > or the binary representation of the value... but that would not help > much (I don't want to deal with internal structures!). Yes, stay away from binary types if at all possible! > Also: I still have the overflow issue with types like INTERVAL SECOND. > => discussed in another thread "INTERVAL SECOND limited to 59 seconds?" If I read it correctly; it's not overflow but truncation. In PG (I've got no idea what the database you're comparing to does/what the spec says it's supposed to do) when you you want a value of type "INTERVAL SECOND" then PG is interpreting this as meaning I want a value of type INTERVAL where all the fields apart from the seconds are zero. Whether this is useful seems debatable, Richard's suggestion of creating a set of custom types that do the right thing for you seems like the path of least resistance. -- Sam http://samason.me.uk/ -- 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] my insertion script don't work
kelvin273 wrote: Hi all, i'm using postgresql 8.3 and i have create a very simple script for insert data into database (because i'm testing it, i create and drop/mod frequently the tables). My script is this: #!/bin/bash echo "Insert start" psql -U myself -d test -f 000_ins_users.sql psql -U myself -d test -f 001_ins_profile.sql psql -U myself -d test -f 002_ins_user_prof.sql echo "Insert end" but i receive this error when i launch this: Insert start : Invalid argument : Invalid argumentl : Invalid argumentsql I suspect a stray carriage-return (\r) somewhere. I think the "Invalid argument" is at the end of the message and is preceded by the carriage-return. Did you edit the .sql files in Windows perhaps? -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Restoring a database from original files (Was: Need help
On Mon, May 18, 2009 at 11:33:03PM +0430,
Zico wrote
a message of 74 lines which said:
> No, I don`t have any data of Postgres "data" directory.
Next time, do not forget backups...
> As far as i can remember, my postgre files were in /usr/share/postgresql/8.3
>
> as i am using the Debian distro.
In that case, they were (that's the default location) in
/var/lib/postgresql/$VERSION/$CLUSTERNAME
Many people probably assumed you use MS-Windows because of your
difficulties to provide hard information, or to set a proper subject
(I fixed that).
> I don`t know, what should i do! Because, i have only the softcopy of
> my data, nothing else. No, postgresql directory, no dumped sql
> file!! :(
OK, if I read correctly the whole thread, you have binary documents
(in formats like PDF or MS-Word) and they were in the past inserted
into the database. Now, the database is gone and you want to insert
them again? Correct?
If so, first a question, how were these documents inserted? By a
program? If so, you simply have to run this program again.
Did you put the whole file in a PostgreSQL field of type "bytea" or
was there some process to extract from the files the relevant info? In
the last case, was the process manual or by a program?
I suspect that it is complicated and that these files were inserted by
hand, may be after manual extraction of the fields? Correct? If so, by
direct SQL INSERT statements or through some interface?
If the files were inserted by hand, and you don't want to do it again
for the 2000 documents, the only solution is to write a program that
will perform the insertion again.
It can be as simple as the following Python script which inserts into
the database all the files mentioned on its command line:
#!/usr/bin/python
#CREATE TABLE PDF_files (id SERIAL UNIQUE NOT NULL,
#added TIMESTAMP NOT NULL DEFAULT now(),
#name TEXT UNIQUE NOT NULL,
#value BYTEA NOT NULL);
import psycopg
import sys
connection = psycopg.connect("dbname=essais")
cursor = connection.cursor()
for filename in sys.argv[1:]:
cursor.execute("INSERT INTO PDF_files (name, value) VALUES (%s, %s);",
(filename, psycopg.Binary(open(filename).read(
cursor.execute("COMMIT;")
connection.close()
--
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] my insertion script don't work
In response to kelvin273 : > > Hi all, > i'm using postgresql 8.3 and i have create a very simple script for insert > data into database (because i'm testing it, i create and drop/mod frequently > the tables). > My script is this: > > #!/bin/bash > echo "Insert start" > psql -U myself -d test -f 000_ins_users.sql > psql -U myself -d test -f 001_ins_profile.sql > psql -U myself -d test -f 002_ins_user_prof.sql > echo "Insert end" > > but i receive this error when i launch this: > > Insert start > : Invalid argument > : Invalid argumentl > : Invalid argumentsql > Insert end > > It looks like that the filename argument is not ok...and the error is > different because the filename (length of that) is different...can you help > me, please? looks okay, maybe the sql-files are wrong? kretsch...@tux:~$ echo "select current_date" > date.sql kretsch...@tux:~$ psql test -f date.sql date 2009-05-19 (1 row) I'm sure, your files contains invalid sql-code. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- 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] Get block of N numbers from sequence
hubert depesz lubaczewski schrieb:
> On Tue, May 19, 2009 at 01:45:17PM +0200, Thomas Guettler wrote:
>> how can you get N numbers (without holes) from a sequence?
>
> alter sequence XXX increment by 1000;
> select nextval('XXX');
> alter sequence XXX increment by 1;
If other processes run nextval() between "increment by 1000" and "increment by
1",
they leave big holes in the sequence.
Unfortunately begin; ... rollback; does not help. the "alter sequence" command
gets
executed, even if the transaction gets rolled back.
db=# begin; alter SEQUENCE foo_seq increment by 100; rollback;
BEGIN
ALTER SEQUENCE
ROLLBACK
db=# select * from foo_seq;
sequence_name | last_value | increment_by | max_value |
min_value | cache_value | log_cnt |
is_cycled | is_called
++--+-+---+-+-+---+---
foo_seq | 90508740 | 100 | 9223372036854775807 | 1 |
1 | 6 | f | t
db=# select version();
PostgreSQL 8.2.6 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.2.1
(SUSE Linux)
Thomas
--
Thomas Guettler, http://www.thomas-guettler.de/
E-Mail: guettli (*) thomas-guettler + de
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] my insertion script don't work
Hi all, i'm using postgresql 8.3 and i have create a very simple script for insert data into database (because i'm testing it, i create and drop/mod frequently the tables). My script is this: #!/bin/bash echo "Insert start" psql -U myself -d test -f 000_ins_users.sql psql -U myself -d test -f 001_ins_profile.sql psql -U myself -d test -f 002_ins_user_prof.sql echo "Insert end" but i receive this error when i launch this: Insert start : Invalid argument : Invalid argumentl : Invalid argumentsql Insert end It looks like that the filename argument is not ok...and the error is different because the filename (length of that) is different...can you help me, please? Thank you very much! -- View this message in context: http://www.nabble.com/my-insertion-script-don%27t-work-tp23611570p23611570.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INTERVAL data type and libpq - what format?
Yes, good point. I realize now that I would have expected libpq to give me a way to specify the exact decoration or precision of INTERVAL parameters... As you can do with ODBC's SQLBindParameter(), where you specify the C type, SQL type, precision/scale or length ... I believe this is important when it comes to data type conversion (for ex, when you want to insert a numeric/date/time into a char or the other way). => sort of cast, actually... I known libpq functions like PQexecParams() or PQexecPrepared() have a paramFormats[] parameter to specify if the buffer will hold a string or the binary representation of the value... but that would not help much (I don't want to deal with internal structures!). I can manage to bind only basic INTERVAL values for all sort of INTERVAL columns, no problem... I did further tests using the "PnnnYnnnM ..." ISO format and that is working much better. However I would expect at least 2 classes of INTERVALs to be specified in libpq parameters: INTERVAL YEAR TO MONTH INTERVAL DAY TO SECOND(n) Also: I still have the overflow issue with types like INTERVAL SECOND. => discussed in another thread "INTERVAL SECOND limited to 59 seconds?" Thanks a lot! Seb Sam Mason wrote: On Tue, May 19, 2009 at 10:08:37AM +0200, Sebastien FLAESCH wrote: I try to use the new 8.4 INTERVAL type with libpq, but get crazy with the input formatting rules... I think you're giving the database conflicting instructions and it's getting confused. fprintf(stdout,"++ Preparing INSERT ...\n"); paramTypes[0] = 23; /* INT4 */ paramTypes[1] = 1186; /* INTERVAL */ paramTypes[2] = 1186; /* INTERVAL */ I don't really know 8.4, but I believe you're saying here that you explicitly want the values to be of basic INTERVAL type here, i.e. not INTERVAL DAY TO HOUR for parameter 3. Thus when you do: paramValues[0] = "1"; paramValues[1] = "-12345 years"; paramValues[2] = " 123 11:00"; r = PQexecPrepared(c, "s1", 3, paramValues, NULL, NULL, 0); It's interpreting " 123 11:00" correctly as a basic INTERVAL value and then casting it to your more constrained version as you're saving in the table. However, when you do: paramValues[0] = "2"; paramValues[1] = "-12345"; paramValues[2] = " 123 11"; r = PQexecPrepared(c, "s1", 3, paramValues, NULL, NULL, 0); You get an error because " 123 11" isn't a valid literal of an (undecorated) INTERVAL type. I think PG may do the right thing if you don't specify the types when preparing the query, but haven't tested. -- 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] Get block of N numbers from sequence
On Tue, May 19, 2009 at 12:45 PM, Thomas Guettler wrote: > Hi, > > how can you get N numbers (without holes) from a sequence? generate_series(), or make sure there's only one connection, no transactions rollback. -- GJ -- 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] Where is tsearch2.sql
Raymond O'Donnell wrote: On 18/05/2009 23:28, Howard Cole wrote: Just installed 8.3.7 on a W2K3 machine using the pginstaller. I cannot find contrib/tsearch2.sql - I need to import an 8.2 backup with the old tsearch2 but cannot find the new compatibility layer sql file. Hi there, tsearch2 was incorporated into the PostgreSQL core as of version 8.3, so you won't find it in contrib any more. :-) Ray. Thanks Ray - but according to the postgres manual (8.3.7) there is a SQL file (contrib/tsearch2.sql) which allows me to import backups that were created with the old tsearch2 (v8.2). I know this because I have used it before - however this file no longer appears in the latest pginstaller. Howard. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Get block of N numbers from sequence
Hi, how can you get N numbers (without holes) from a sequence? Thomas -- Thomas Guettler, http://www.thomas-guettler.de/ E-Mail: guettli (*) thomas-guettler + de -- 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] Where is tsearch2.sql
On 18/05/2009 23:28, Howard Cole wrote: > Just installed 8.3.7 on a W2K3 machine using the pginstaller. I cannot > find contrib/tsearch2.sql - I need to import an 8.2 backup with the old > tsearch2 but cannot find the new compatibility layer sql file. Hi there, tsearch2 was incorporated into the PostgreSQL core as of version 8.3, so you won't find it in contrib any more. :-) Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie 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] INTERVAL data type and libpq - what format?
On Tue, May 19, 2009 at 10:08:37AM +0200, Sebastien FLAESCH wrote: > I try to use the new 8.4 INTERVAL type with libpq, but get crazy with > the input formatting rules... I think you're giving the database conflicting instructions and it's getting confused. > fprintf(stdout,"++ Preparing INSERT ...\n"); > paramTypes[0] = 23; /* INT4 */ > paramTypes[1] = 1186; /* INTERVAL */ > paramTypes[2] = 1186; /* INTERVAL */ I don't really know 8.4, but I believe you're saying here that you explicitly want the values to be of basic INTERVAL type here, i.e. not INTERVAL DAY TO HOUR for parameter 3. Thus when you do: > paramValues[0] = "1"; > paramValues[1] = "-12345 years"; > paramValues[2] = " 123 11:00"; > r = PQexecPrepared(c, "s1", 3, paramValues, NULL, NULL, 0); It's interpreting " 123 11:00" correctly as a basic INTERVAL value and then casting it to your more constrained version as you're saving in the table. However, when you do: > paramValues[0] = "2"; > paramValues[1] = "-12345"; > paramValues[2] = " 123 11"; > r = PQexecPrepared(c, "s1", 3, paramValues, NULL, NULL, 0); You get an error because " 123 11" isn't a valid literal of an (undecorated) INTERVAL type. I think PG may do the right thing if you don't specify the types when preparing the query, but haven't tested. -- Sam http://samason.me.uk/ -- 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] INTERVAL SECOND limited to 59 seconds?
Sebastien FLAESCH wrote: Actually it's not limited to the usage of INTERVAL SECOND, I am writing a PostgreSQL driver for our 4GL virtual machine... I need to store all possible Informix INTERVAL types such as: INTERVAL MONTH(8) TO MONTH INTERVAL DAY(8) TO MINUTE INTERVAL SECOND TO FRACTION(5) ... etc ... ... If PostgreSQL is not able to store months > 11, hours > 23 and minutes or seconds > 59 Well, it's not storage it's formatting. Doesn't make any difference to your problem though. >, it looks like I will need to deal with PostgreSQL's INTERVAL YEAR TO MONTH INTERVAL DAY TO SECOND(5) ... and make conversions, to store all possible Informix INTERVALs... If you know a little "C" you could build some custom types to match your needs. It should just be a matter of applying the correct formatting as a wrapper around the existing "interval" type. -- Richard Huxton Archonet Ltd -- 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] INTERVAL SECOND limited to 59 seconds?
Actually it's not limited to the usage of INTERVAL SECOND, I am writing a PostgreSQL driver for our 4GL virtual machine... I need to store all possible Informix INTERVAL types such as: INTERVAL MONTH(8) TO MONTH INTERVAL DAY(8) TO MINUTE INTERVAL SECOND TO FRACTION(5) ... etc ... ... If PostgreSQL is not able to store months > 11, hours > 23 and minutes or seconds > 59, it looks like I will need to deal with PostgreSQL's INTERVAL YEAR TO MONTH INTERVAL DAY TO SECOND(5) ... and make conversions, to store all possible Informix INTERVALs... Seb -- 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] INTERVAL SECOND limited to 59 seconds?
Sebastien FLAESCH wrote:
I think it should be clarified in the documentation...
Please don't top-quote. And yes, I think you're right.
Hmm a quick google for: [sql "interval second"] suggests that it's not
the right thing. I see some mention of 2 digit precision for a leading
field, but no "clipping".
Looking at the manuals and indeed a quick \dT I don't see "interval
second" listed as a separate type though. A bit of exploring in
pg_attribute with a test table suggests it's just using "interval" with
a type modifier. Which you seem to confirm from the docs:
> The PostgreSQL documentation says:
>
> The interval type has an additional option, which is to restrict the set
> of stored
> fields by writing one of these phrases:
>
> YEAR
> MONTH
...
> Does that mean that the [field] option of the INTERVAL type is just
> there to save
> storage space?
My trusty copy of the 8.3 source suggests that AdjustIntervalForTypmod()
is the function we're interested in and it lives in
backend/utils/adt/timestamp.c - it looks like it just zeroes out the
fields you aren't interested in. No space saving.
So - not a bug, but perhaps not the behaviour you would expect.
Actually I would like to use this new INTERVAL type to store
IBM/Informix INTERVALs,
which can actually be used like this with DATETIME types:
> create table t1 (
> k int,
> dt1 datetime hour to minute,
> dt2 datetime hour to minute,
> i interval hour(5) to minute );
Table created.
> insert into t1 values ( 1, '14:45', '05:10', '-145:10' );
1 row(s) inserted.
> select dt1 - dt2 from t1;
(expression)
9:35<- INTERVAL expression
SELECT ('14:45'::time - '05:10'::time);
?column?
--
09:35:00
(1 row)
> select 15 * ( dt1 - dt2 ) from t1;
(expression)
143:45<- INTERVAL expressio
=> SELECT 15 * ('14:45'::time - '05:10'::time);
?column?
---
143:45:00
(1 row)
If you can live with the zero seconds appearing, it should all just
work*. Other than formatting as text, I don't know of a way to suppress
them though.
* Depending on whether you need to round up if you ever get odd seconds etc.
--
Richard Huxton
Archonet Ltd
--
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] referring to calculated column in sub select
On Tue, May 19, 2009 at 12:04:28AM -0700, Scara Maccai wrote: > Using Mysql the query works, so I expected it to be somehow "standard" > (I know, Mysql sometimes allows much more than the standard...) No, as far as I know the standard explicitly says that the MySQL behaviour is incorrect. > Does this has something to do with Support LATERAL subqueries" in the > to do list? No, a lateral sub-query would be something like the following: SELECT f.a, b.b FROM foo a, getrows(a.a) b(b); I.e. in order to execute the "getrows" function you need to have values out of "foo" first. You can do this in some specific instances at the moment in PG, but not in general. > It's a very shame it's not supported... another rewrite of your query would be to have a sub-select: SELECT acoltest, (SELECT max(t) FROM mytab WHERE anothercol=acoltest) as col2 FROM (SELECT somet, acol+100 AS acoltest FROM mytab2) GROUP BY somet; Hope that helps! -- Sam http://samason.me.uk/ -- 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] INTERVAL SECOND limited to 59 seconds?
I think it should be clarified in the documentation... Actually I would like to use this new INTERVAL type to store IBM/Informix INTERVALs, which can actually be used like this with DATETIME types: > create table t1 ( > k int, > dt1 datetime hour to minute, > dt2 datetime hour to minute, > i interval hour(5) to minute ); Table created. > insert into t1 values ( 1, '14:45', '05:10', '-145:10' ); 1 row(s) inserted. > select dt1 - dt2 from t1; (expression) 9:35<- INTERVAL expression 1 row(s) retrieved. > select 15 * ( dt1 - dt2 ) from t1; (expression) 143:45<- INTERVAL expression 1 row(s) retrieved. The PostgreSQL documentation says: The interval type has an additional option, which is to restrict the set of stored fields by writing one of these phrases: YEAR MONTH DAY HOUR MINUTE SECOND YEAR TO MONTH DAY TO HOUR DAY TO MINUTE DAY TO SECOND HOUR TO MINUTE MINUTE TO SECOND Does that mean that the [field] option of the INTERVAL type is just there to save storage space? Confusing... Seb Richard Huxton wrote: Sebastien FLAESCH wrote: Hello, Can someone explain this: test1=> create table t1 ( k int, i interval second ); CREATE TABLE test1=> insert into t1 values ( 1, '-67 seconds' ); INSERT 0 1 test1=> insert into t1 values ( 2, '999 seconds' ); INSERT 0 1 test1=> select * from t1; k | i ---+--- 1 | -00:00:07 2 | 00:00:39 (2 rows) I would expect that an INTERVAL SECOND can store more that 59 seconds. I didn't even know we had an "interval second" type. It's not entirely clear to me what such a value means. Anyway - what's happening is that it's going through "interval" first. So - '180 seconds' will yield '00:03:00' and the seconds part of that is zero. The question I suppose is whether that's correct or not. An interval can clearly store periods longer than 59 seconds. It's reasonable to ask for an interval to be displayed as "61 seconds". If "interval second" means the seconds-only part of an interval though, then it's doing the right thing. -- 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] INTERVAL SECOND limited to 59 seconds?
Sebastien FLAESCH wrote: Hello, Can someone explain this: test1=> create table t1 ( k int, i interval second ); CREATE TABLE test1=> insert into t1 values ( 1, '-67 seconds' ); INSERT 0 1 test1=> insert into t1 values ( 2, '999 seconds' ); INSERT 0 1 test1=> select * from t1; k | i ---+--- 1 | -00:00:07 2 | 00:00:39 (2 rows) I would expect that an INTERVAL SECOND can store more that 59 seconds. I didn't even know we had an "interval second" type. It's not entirely clear to me what such a value means. Anyway - what's happening is that it's going through "interval" first. So - '180 seconds' will yield '00:03:00' and the seconds part of that is zero. The question I suppose is whether that's correct or not. An interval can clearly store periods longer than 59 seconds. It's reasonable to ask for an interval to be displayed as "61 seconds". If "interval second" means the seconds-only part of an interval though, then it's doing the right thing. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how the planner decides between bitmap/index scan
Ops, found it:
http://www.postgresql.org/docs/8.4/static/view-pg-stats.html
("correlation" column)
I guess it would be worth pointing it out in the planner docs...
Scara Maccai wrote:
> Hi,
>
> does the planner know how "scattered" rows are? If yes: where is this
> info stored?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] INTERVAL SECOND limited to 59 seconds?
Hello,
Can someone explain this:
test1=> create table t1 ( k int, i interval second );
CREATE TABLE
test1=> insert into t1 values ( 1, '-67 seconds' );
INSERT 0 1
test1=> insert into t1 values ( 2, '999 seconds' );
INSERT 0 1
test1=> select * from t1;
k | i
---+---
1 | -00:00:07
2 | 00:00:39
(2 rows)
I would expect that an INTERVAL SECOND can store more that 59 seconds.
Same question for INTERVAL MINUTE TO SECOND (but here we get an overflow error):
test1=> create table t2 ( k int, i interval minute to second );
CREATE TABLE
test1=> insert into t2 values ( 2, ':59' );
ERROR: interval field value out of range: ":59"
LINE 1: insert into t2 values ( 2, ':59' );
^
test1=> insert into t2 values ( 2, '999:59' );
ERROR: interval field value out of range: "999:59"
LINE 1: insert into t2 values ( 2, '999:59' );
^
test1=> insert into t2 values ( 2, '99:59' );
ERROR: interval field value out of range: "99:59"
LINE 1: insert into t2 values ( 2, '99:59' );
^
test1=> insert into t2 values ( 1, '59:59' );
INSERT 0 1
test1=> insert into t2 values ( 2, '-123:59' );
INSERT 0 1
test1=> select * from t2;
k | i
---+---
1 | 00:59:59
2 | -00:59:00
(2 rows)
It's ok when using DAYs:
test1=> create table t3 ( k int, i interval day to second );
CREATE TABLE
test1=> insert into t3 values ( 1, '- 18:59:59' );
INSERT 0 1
test1=> insert into t3 values ( 1, '999 18:59:59' );
INSERT 0 1
test1=> select * from t3;
k | i
---+---
1 | - days +18:59:59
1 | 999 days 18:59:59
(2 rows)
Thanks a lot!
Seb
/*
Version:8.4.beta1
Created by: s...@4js.com
Problem with INTERVAL input format
--
After executing this program, 2 rows are present in the table.
Only the first has the expected values...
Why does the second insert fail to insert "123 11" in INTERVAL DAY TO HOUR?
Diagnostic info:
SQL State: 22007
Message : invalid input syntax for type interval: " 123 11"
Why does the third row show "00:00:00" in first INTERVAL YEAR column?
[...@fox problems]$ psql test1 -U pgsuser
psql (8.4beta1)
Type "help" for help.
test1=> select * from t1;
k | i1 |i2
---+--+---
1 | -12345 years | 123 days 11:00:00
3 | 00:00:00 | 123 days 11:00:00
(2 rows)
When inserting rows with psql, the format used by the C program are supported:
test1=> insert into t1 values ( 4, '-12345', '123 11' );
INSERT 0 1
test1=> select * from t1 where k=4;
k | i1 |i2
---+--+---
4 | -12345 years | 123 days 11:00:00
(1 row)
So what am I doing wrong here?
*/
#include
#include
static int checkResult(PGresult * r)
{
if (r == NULL)
return 0;
switch (PQresultStatus(r)) {
case PGRES_COMMAND_OK:
case PGRES_TUPLES_OK:
return 1;
default:
return 0;
}
}
static void getErrorInfo(PGresult * r)
{
if (r == NULL)
return;
fprintf(stderr, "Diagnostic info:\n");
fprintf(stderr, " SQL State: %s\n", PQresultErrorField(r,
PG_DIAG_SQLSTATE));
fprintf(stderr, " Message : %s\n", PQresultErrorField(r,
PG_DIAG_MESSAGE_PRIMARY));
}
int main(int argc, char **argv)
{
PGresult *r;
PGconn *c;
Oid paramTypes[10];
const char *paramValues[10];
fprintf(stdout,"++ Connecting...\n");
c = PQconnectdb("dbname='test1' user='pgsuser' password='fourjs'");
if (c == NULL) {
fprintf(stderr,">> Could not connect.\n");
exit(1);
}
fprintf(stdout,"++ Creating table t1 ...\n");
r = PQexec(c, "DROP TABLE t1");
PQclear(r);
r = PQexec(c, "CREATE TABLE t1 ( k INT, i1 INTERVAL YEAR, i2 INTERVAL DAY
TO HOUR)");
if (!checkResult(r)) {
fprintf(stderr,">> Could not create table 1.\n");
getErrorInfo(r);
exit(1);
}
PQclear(r);
fprintf(stdout,"++ Preparing INSERT ...\n");
paramTypes[0] = 23; /* INT4 */
paramTypes[1] = 1186; /* INTERVAL */
paramTypes[2] = 1186; /* INTERVAL */
r = PQprepare(c, "s1",
"INSERT INTO t1 VALUES ( $1, $2, $3 )",
3, (const Oid *) paramTypes);
if (!checkResult(r)) {
fprintf(stderr,">> Could not prepare stmt 1.\n");
getErrorInfo(r);
exit(1);
}
PQclear(r);
/* This is working */
fprintf(stdout,"++ Executing INSERT (1) ...\n");
paramValues[0] = "1";
paramValues[1] = "-12345 years";
paramValues[2] = " 123 11:00";
r = PQexecPrepared(c, "s1", 3, paramValues, NULL, NULL, 0);
if (!checkResult(r)) {
fprintf(stderr,">> Could not exec stmt 1.\n");
getErrorInfo(r);
exit(1);
}
PQclear(r);
/* This is NOT working */
fprintf(stdout,"++ Executing INSERT (2) ...\n");
paramValues[0] = "2";
paramValues[1] = "-12345";
paramValues[2] = " 12
[GENERAL] how the planner decides between bitmap/index scan
Hi, does the planner know how "scattered" rows are? If yes: where is this info stored? I'm asking because it looks to me that not only the number of rows, but also how data is on disk (more or less sequential) is used to determine what type of index scan (bitmap/regular) has to be used... but in the docs: http://www.postgresql.org/docs/8.4/static/using-explain.html I've found only "there are so few that the extra cost of sorting the row locations is not worth it", which would mean that only the number of rows is actually used to choose between the two index access methods. Can someone explain? Thank you -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] INTERVAL data type and libpq - what format?
Hello,
I try to use the new 8.4 INTERVAL type with libpq, but get crazy with
the input formatting rules...
I use PQprepare() / PQexecPrepared() with parameter list, binding the
INTERVAL values with the 1186 pg_type and passing a string buffer with
values like:
"12345" for an INTERVAL YEAR
The INSERT works without error, but when selecting rows from the table
in psql, I get "00:00:00" values ?!?!
When inserting the value "12345" from the psql command tool it works...
I must be doing something wrong, but I could not find any documentation
on using INTERVAL in libpq...
Can someone from the hackers just tell me if it's supposed to work and
if yes what format is expected by the client library?
Attached, you have a test case to reproduce the problem.
Thanks a lot!
Seb
/*
Version:8.4.beta1
Created by: s...@4js.com
Problem with INTERVAL input format
--
After executing this program, 2 rows are present in the table.
Only the first has the expected values...
Why does the second insert fail to insert "123 11" in INTERVAL DAY TO HOUR?
Diagnostic info:
SQL State: 22007
Message : invalid input syntax for type interval: " 123 11"
Why does the third row show "00:00:00" in first INTERVAL YEAR column?
[...@fox problems]$ psql test1 -U pgsuser
psql (8.4beta1)
Type "help" for help.
test1=> select * from t1;
k | i1 |i2
---+--+---
1 | -12345 years | 123 days 11:00:00
3 | 00:00:00 | 123 days 11:00:00
(2 rows)
When inserting rows with psql, the format used by the C program are supported:
test1=> insert into t1 values ( 4, '-12345', '123 11' );
INSERT 0 1
test1=> select * from t1 where k=4;
k | i1 |i2
---+--+---
4 | -12345 years | 123 days 11:00:00
(1 row)
So what am I doing wrong here?
*/
#include
#include
static int checkResult(PGresult * r)
{
if (r == NULL)
return 0;
switch (PQresultStatus(r)) {
case PGRES_COMMAND_OK:
case PGRES_TUPLES_OK:
return 1;
default:
return 0;
}
}
static void getErrorInfo(PGresult * r)
{
if (r == NULL)
return;
fprintf(stderr, "Diagnostic info:\n");
fprintf(stderr, " SQL State: %s\n", PQresultErrorField(r,
PG_DIAG_SQLSTATE));
fprintf(stderr, " Message : %s\n", PQresultErrorField(r,
PG_DIAG_MESSAGE_PRIMARY));
}
int main(int argc, char **argv)
{
PGresult *r;
PGconn *c;
Oid paramTypes[10];
const char *paramValues[10];
fprintf(stdout,"++ Connecting...\n");
c = PQconnectdb("dbname='test1' user='pgsuser' password='fourjs'");
if (c == NULL) {
fprintf(stderr,">> Could not connect.\n");
exit(1);
}
fprintf(stdout,"++ Creating table t1 ...\n");
r = PQexec(c, "DROP TABLE t1");
PQclear(r);
r = PQexec(c, "CREATE TABLE t1 ( k INT, i1 INTERVAL YEAR, i2 INTERVAL DAY
TO HOUR)");
if (!checkResult(r)) {
fprintf(stderr,">> Could not create table 1.\n");
getErrorInfo(r);
exit(1);
}
PQclear(r);
fprintf(stdout,"++ Preparing INSERT ...\n");
paramTypes[0] = 23; /* INT4 */
paramTypes[1] = 1186; /* INTERVAL */
paramTypes[2] = 1186; /* INTERVAL */
r = PQprepare(c, "s1",
"INSERT INTO t1 VALUES ( $1, $2, $3 )",
3, (const Oid *) paramTypes);
if (!checkResult(r)) {
fprintf(stderr,">> Could not prepare stmt 1.\n");
getErrorInfo(r);
exit(1);
}
PQclear(r);
/* This is working */
fprintf(stdout,"++ Executing INSERT (1) ...\n");
paramValues[0] = "1";
paramValues[1] = "-12345 years";
paramValues[2] = " 123 11:00";
r = PQexecPrepared(c, "s1", 3, paramValues, NULL, NULL, 0);
if (!checkResult(r)) {
fprintf(stderr,">> Could not exec stmt 1.\n");
getErrorInfo(r);
exit(1);
}
PQclear(r);
/* This is NOT working */
fprintf(stdout,"++ Executing INSERT (2) ...\n");
paramValues[0] = "2";
paramValues[1] = "-12345";
paramValues[2] = " 123 11";
r = PQexecPrepared(c, "s1", 3, paramValues, NULL, NULL, 0);
if (!checkResult(r)) {
fprintf(stderr,">> Could not exec stmt 2.\n");
getErrorInfo(r);
/*exit(1);*/
}
PQclear(r);
/* This is NOT working */
fprintf(stdout,"++ Executing INSERT (3) ...\n");
paramValues[0] = "3";
paramValues[1] = "-12345";
paramValues[2] = " 123 11:00";
r = PQexecPrepared(c, "s1", 3, paramValues, NULL, NULL, 0);
if (!checkResult(r)) {
fprintf(stderr,">> Could not exec stmt 3.\n");
getErrorInfo(r);
exit(1);
}
PQclear(r);
PQfinish(c);
}
--
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] Commit visibility guarantees
On Mon, 2009-05-18 at 18:44 -0500, Marsh Ray wrote: > This is a connection kept open for extended periods, and used > mutithreadedly for selects only. I have some code that will allow you to reuse snapshots from one session to another, which could help your multiple threads see a consistent view of the database. It isn't updated as yet for 8.2+. Please contact me off-list if you're interested. I hope it will be part of PostgreSQL one day. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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] Commit visibility guarantees
Marsh Ray wrote: >>> The central question: So if I successfully commit an update >>> transaction on one connection, then instantaneously issue a select on >>> another previously-opened connection, under what circumstances am I >>> guaranteed that the select will see the effects of the update? >> >> If the select is using a snapshot taken later than the commit, it will >> see the effects of the update. > > Great! Just the kind of definitive answer I was looking for. > > Now I just need to find a comprehensive list of all the things that > could cause an older snapshot to be retained, and ensure that none of > them could possibly be occurring on this connection. > > This is a connection kept open for extended periods, and used > mutithreadedly for selects only. Do you suppose a long-running > concurrent select on another thread could be holding back the snapshot > for the whole connection? Hmm... You cannot run two selects in one connection at the same time, see http://www.postgresql.org/docs/current/static/libpq-threading.html One connection belongs to one backend process that can do one thing at a time. If you want concurrency, you must use more than one connection. If the isolation mode is "read committed", then the snapshot of the query will be taken at query start time. So there is no need to worry. Yours, Laurenz Albe -- 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] referring to calculated column in sub select
Using Mysql the query works, so I expected it to be somehow "standard" (I know, Mysql sometimes allows much more than the standard...) Does this has something to do with Support LATERAL subqueries" in the to do list? It's a very shame it's not supported... Thank you --- Lun 18/5/09, Sam Mason ha scritto: > Da: Sam Mason > Oggetto: Re: [GENERAL] referring to calculated column in sub select > A: pgsql-general@postgresql.org > Data: Lunedì 18 maggio 2009, 19:10 > On Mon, May 18, 2009 at 06:49:30AM > -0700, Scara Maccai wrote: > > why column "acoltest" is not found by the subselect in > this select: > > The "acoltest" identifier is only visible from outside the > query, not > within its defining query or any of its sub-queries. > If you're trying > to solve a problem like the example, it would probably be > easiest to > swap the inner and outer queries around, i.e. something > like: > > SELECT acoltest, MAX(b.t) > FROM ( > SELECT acol + 100 as acoltest > FROM mytab2 > GROUP BY 1) a LEFT JOIN mytab b ON a.acoltest > = b.anothercol > GROUP BY acoltest; > > -- > Sam http://samason.me.uk/ > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
