[GENERAL] Daylight saving time question
How can I tell PostgreSQL to use daylight saving time when applicable? Times returned by the database are one hour behind. TIA Bayless
Re: [GENERAL] Daylight saving time question
Bayless Kirtley wrote: How can I tell PostgreSQL to use daylight saving time when applicable? Times returned by the database are one hour behind. it uses your client's specified local time zone to determine whether or not DST is in effect. SET TIME ZONE 'America/New York'; or SET TIME ZONE 'PST8PDT'; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Is this a bug or a feature? Column visibility in subquery from outer query
postgres=# create table public.ps_test_x (x1 oid, x2 oid, x3 oid); CREATE TABLE postgres=# create table public.ps_test_y (y1 oid, y2 oid, y3 oid); CREATE TABLE postgres=# explain select * from public.ps_test_x where x1 in (select x1 from public.ps_test_y); QUERY PLAN - Seq Scan on ps_test_x (cost=0.00..28462.75 rows=885 width=12) Filter: (subplan) SubPlan - Seq Scan on ps_test_y (cost=0.00..27.70 rows=1770 width=0) (4 rows) postgres=# explain select * from public.ps_test_x where x1 in (select x2 from public.ps_test_y); QUERY PLAN - Seq Scan on ps_test_x (cost=0.00..28462.75 rows=885 width=12) Filter: (subplan) SubPlan - Seq Scan on ps_test_y (cost=0.00..27.70 rows=1770 width=0) (4 rows) postgres=# explain select * from public.ps_test_x where x1 in (select x3 from public.ps_test_y); QUERY PLAN - Seq Scan on ps_test_x (cost=0.00..28462.75 rows=885 width=12) Filter: (subplan) SubPlan - Seq Scan on ps_test_y (cost=0.00..27.70 rows=1770 width=0) (4 rows) postgres=# explain select * from public.ps_test_x where x1 in (select x4 from public.ps_test_y); ERROR: column x4 does not exist LINE 1: ...elect * from public.ps_test_x where x1 in (select x4 from pu... ^ postgres=# explain select * from public.ps_test_x where x1 in (select y1 from public.ps_test_y); QUERY PLAN --- Hash Join (cost=36.62..88.66 rows=1770 width=12) Hash Cond: (ps_test_x.x1 = ps_test_y.y1) - Seq Scan on ps_test_x (cost=0.00..27.70 rows=1770 width=12) - Hash (cost=34.12..34.12 rows=200 width=4) - HashAggregate (cost=32.12..34.12 rows=200 width=4) - Seq Scan on ps_test_y (cost=0.00..27.70 rows=1770 width=4) (6 rows) I just want to point out that the sub-query is using a column from the outer query (eg. x1) without an alias from the table in the outer query. This can lead to a confusion when, for example: delete from table1 where foreign_id in (select foreign_id from table2) -- ! table2 does not have the foreign_id column ! This would do a table scan on table1 and delete all its rows. Why isn't it like: delete from table1 where foreign_id in (select table1.foreign_id from table2) where you must specify the outer query's table reference inside the subquery. I suspect the original intent was to use the outer query columns in some processing inside the subquery, which is valid. I'm just wondering why an explicit reference isn't required to distinguish the column. For convenience, or part of the SQL spec? Cheers! --Paolo Saul
Re: [GENERAL] [fulltext]Gin index full scan
On Mon, May 11, 2009 at 8:04 PM, esemba ese...@gmail.com wrote:
I've a table with tsvector column named meta_vector. Over this column there
is a gin index. When I execute query like:
select id from theses where meta_vector @@@ to_tsquery('cs', 'foo | (!bar)')
I get an errror message: query requires full scan, which is not supported by
GIN indexes.
The weird thing is, that when I drop the index, or disable index scans, it
works. Why can't the planner just use full scans instead of index scans on
such the queries? Thanks for help.
You can search the archives for the last time this was brought up.
Apparently, it isn't an easy fix. People hoped to have it addressed
for 8.4 but I don't know if it made it. Unfortunately, this makes GIN
indexes unusable for many applications such as text searching using
arbitrary user queries. GIST indexes work, but perform worse for
reads.
--
Stuart Bishop stu...@stuartbishop.net
http://www.stuartbishop.net/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_dump and 2gb limit on windows and version 8.1.3
Hello, I have problem with 2gb limit for pg_dump on windows that was fixed in version 8.2.4. I have a server with postgresql version 8.1.3, which unfortunately cannot be upgraded. Is there some way how to use safely pg_dump (and if needed pg_restore) version 8.2.4 or newer with the server (8.1.3). Thanks for answer, Regards, Jakub 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] Need help
Zico wrote: our server suddenly went down and after searching, i found out that, the OS crashed. So, i had to re-install my OS again. But, the problem is, I couldnot take the dump file for my postgresql database. Now, i have the raw data, that means, my datas are in a disk. Is it possible to restore those data here in new server? Copy all the files that belong to the PostgreSQL server (everything under the data directory) to a working machine with PostgreSQL installed, and you should be able to start the server there. And remember to keep backups from now on. 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] Excel and pg
On Mon, 18 May 2009 09:14:41 +0800 Craig Ringer cr...@postnewspapers.com.au wrote: Ivan Sergio Borgonovo wrote: I'd like to know if: - it is possible to load in an Excel sheet a table (view, query result) coming from postgresql and to use those data to do further computation/presentation work on Excel? Certainly. You can do it through the ODBC interface via VB, and I think Excel also has some kind of data browser that lets the user pull data from ODBC-accessed databases interactively. This is working. I had a bit of fun trying to find the right menu in the Chinese version. For the uninitiated/the ones that don't have fresh memory of working in a MS environment: - Install Postgresql and ODBC drivers and create a DB/user etc... - Create a system wide DSN connection. In XP is a bit hidden compared to my memories of W2K (Control Panel - Performance an Maintenance - Administrative Tools - Data Source) - Open Excel, there should be a Data Menu... I can't go further since the Excel was localised in Chinese. There are some menu that follow but we weren't able to read them maybe due to the mismatch of OS and Excel localisation. You can import tables and view too and it seems you can apply a SQL filter on them. Dates seems to be imported correctly and I think localised according the setting of the OS (I'd ask, I think in mainland China data should follow the European format and not the UK/US format). Beware, though. Excel has funny ideas about dates and has some baked-in bugs in some of its functions. It doesn't know about or respect the foreign key relationships and constraints in the database, either. Fortunately I won't delegate anything related to data integrity to Excel. I'll keep an eye on dates. Having had some experience with MS SQL I had some difficulties with converting in and out dates from Postgresql at times. I know it shouldn't be the responsibility of the DB to convert between formats... but for some dirty works it can really comes handy. pg - Excel worked perfectly. I hope Excel - csv - pg will be equally painless. I'm a bit worried considering the limited toolset I can rely on I may have some localisation problems when people will try to save Excel - csv. COPY may not support all the flexibility I need if Chinese localised Excel/OS will output some strange stuff in csv. I chose to pass through pg just because I have to help to write down some business logic for reporting and I bet I'll have to get back at what I wrote in 1-6 months. I tried to implement some of the reporting logic in Excel but: - Something really fit SQL - I don't want to relearn how an IF works, especially if I have to switch back and forward to Polish notation - I've found something really weird. People say SQL is hard (yeah it may be...) but that S really shine once you compare it with the way to operate of a spread sheet ;) - Office SQL is a PITA. I gave up once I saw they don't implement COALESCE. If people would like to elaborate further on data coming from pg using Excel functions they will be on their own. If you really must pull data into Excel, consider giving users an account in PostgreSQL that _ONLY_ has access to read-only views of the data. Those views should denormalize the data significantly and otherwise make it as Excel-friendly as possible. Pull the data in using a Visual Basic script that protects the data as soon as it's been placed on the sheets, so the user can't accidentally change it, just reference it. I've to import data in Postgresql... that comes in other Excel files. I can't rely on a complete programming environment. I was thinking about opening another ODBC connection and using dblink to import data from Excel to pg... but somehow it doesn't look it is going to really improve the procedure of importing data from a csv. I mean... someone doesn't do it properly (eg. some kind of lock/whatever on the Excel file) people won't be able to understand the problem. Saving the Excel file to csv seems something could be handled easier by the people that will do the job. I think that somehow refreshing data imported by Excel is going to run the associated query... so if I write a function that run \copy and place a select * from importdata() in Excel... everything people should do is save the excel as csv somewhere. - can postgresql load data from an Excel sheet? Or Excel write data to postgresql from an excel sheet? dblink? The easiest way is via CSV. You could probably also do it with some Visual Basic running in Excel that pushes the data via ODBC. If you're going to even vaguely consider putting data from a user-modifiable spreadsheet back in the DB, make sure to protect every cell the user isn't explicitly meant to be able to modify. - am I going to incur in any localisation problem if the Windows stuff is localised in Chinese? I see I can chose the language to be used during installation. I'd prefer localization to be in
Re: [GENERAL] Need help
On Mon, May 18, 2009 at 1:24 PM, Albe Laurenz laurenz.a...@wien.gv.atwrote: Copy all the files that belong to the PostgreSQL server (everything under the data directory) to a working machine with PostgreSQL installed, and you should be able to start the server there. the problem is, i don`t have files of previous PostgreSQL!!! I have only soft copies of my datas like pds, docs and others. Now what? -- Best, Zico
Re: [GENERAL] Need help
Zico wrote: [wants to restore a database without a backup] Copy all the files that belong to the PostgreSQL server (everything under the data directory) to a working machine with PostgreSQL installed, and you should be able to start the server there. the problem is, i don`t have files of previous PostgreSQL!!! I have only soft copies of my datas like pds, docs and others. Now what? Could you explain that in more detail? What is a pds or docs? 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] Need help
On Mon, May 18, 2009 at 4:00 PM, Albe Laurenz laurenz.a...@wien.gv.atwrote: Could you explain that in more detail? What is a pds or docs? PDF == Portable Document Format files Docs == Documents Means, i have the backup of my files only which was in that database. -- Best, Zico
[GENERAL] preforked processes
Hello, I can not find configuration option to set init_children_process. Where I can do it? -- -- -- Publicidad y Servicios http://www.pas-world.com Directorio http://www.precioventa.com Tienda http://informatica.precioventa.com/es/ Autoridad certificadora http://ca.precioventa.com/es/ -- -- -- 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] preforked processes
On Mon, May 18, 2009 at 02:04:46PM +0200, Developer wrote: Hello, I can not find configuration option to set init_children_process. Where I can do it? postgresql doesn't have this option. 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] Need help
Zico wrote: [wants to restore a database without a backup] Could you explain that in more detail? What is a pds or docs? PDF == Portable Document Format files Docs == Documents Means, i have the backup of my files only which was in that database. I'm still not sure what that means, but if you have the contents of the database, but the database itself is gone without a trace, then what else can you do but create a new database and fill it anew? 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
[GENERAL] referring to calculated column in sub select
Hi, why column acoltest is not found by the subselect in this select: SELECT acol + 100 as acoltest, (select max(t) from mytab where anothercol=acoltest) as col2 FROM mytab2 group by somet ??? Only columns belonging to a table can be used in a subselect??? What about calculated columns? 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
Re: [GENERAL] Is this a bug or a feature? Column visibility in subquery from outer query
Paolo Saul paolo.s...@telogis.com writes: I just want to point out that the sub-query is using a column from the outer query (eg. x1) without an alias from the table in the outer query. This behavior is required by the SQL standard. It's called an outer reference. 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] pg_dump and 2gb limit on windows and version 8.1.3
j...@email.cz wrote: Hello, I have problem with 2gb limit for pg_dump on windows that was fixed in version 8.2.4. I have a server with postgresql version 8.1.3, which unfortunately cannot be upgraded. Is there some way how to use safely pg_dump (and if needed pg_restore) version 8.2.4 or newer with the server (8.1.3). Yes -- pg_dump is backwards compatible; it will be able to dump the database just fine. Note, however, that the dump is not guaranteed to be restorable in the 8.1 server. I think you should upgrade to 8.1.17 which likely contains the fix as well. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] pg_dump and 2gb limit on windows and version 8.1.3
Alvaro Herrera alvhe...@commandprompt.com writes: I think you should upgrade to 8.1.17 which likely contains the fix as well. If you are thinking of this fix: 2007-02-19 10:05 mha * src/bin/pg_dump/: pg_backup_archiver.c, pg_backup_archiver.h, pg_backup_custom.c, pg_backup_files.c, pg_backup_tar.c, pg_dump.h (REL8_2_STABLE), pg_backup_archiver.c, pg_backup_archiver.h, pg_backup_custom.c, pg_backup_files.c, pg_backup_tar.c, pg_dump.h: Fix pg_dump on win32 to properly dump files larger than 2Gb when using binary dump formats. it doesn't appear to have been back-patched into 8.1. However the log message suggests a workaround: use plain-text output format. In any case, continuing to depend on an 8.1.x server on Windows (especially one as old as 8.1.3) is just plain stupid. We dropped support for that release series for very good reasons. The OP needs to get off that version --- just accepting someone's diktat that it can't be upgraded is irresponsible, unless the data it contains is of no value (which I doubt or we wouldn't be fielding this question). 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] Excel and pg
On Mon, May 18, 2009 at 11:01:15AM +0200, Ivan Sergio Borgonovo wrote: On Mon, 18 May 2009 09:14:41 +0800 Craig Ringer cr...@postnewspapers.com.au wrote: Ivan Sergio Borgonovo wrote: I'd like to know if: - it is possible to load in an Excel sheet a table (view, query result) coming from postgresql and to use those data to do further computation/presentation work on Excel? Certainly. You can do it through the ODBC interface via VB, and I think Excel also has some kind of data browser that lets the user pull data from ODBC-accessed databases interactively. You can import tables and view too and it seems you can apply a SQL filter on them. Last time I tried doing this you get to write your own SQL if you want; no need to limit yourself to pulling a table out. pg - Excel worked perfectly. I hope Excel - csv - pg will be equally painless. I'm a bit worried considering the limited toolset I can rely on I may have some localisation problems when people will try to save Excel - csv. COPY may not support all the flexibility I need if Chinese localised Excel/OS will output some strange stuff in csv. The correct place to solve this would seem to be in Excel; write some VB code to pull out things in the correct format and put the resulting file somewhere appropriate. - I've found something really weird. People say SQL is hard (yeah it may be...) but that S really shine once you compare it with the way to operate of a spread sheet ;) They're different tools, designed to solve different problems. Spreadsheets are wonderful for making small ad-hoc changes to small datasets, databases are good when you're working on larger or better defined problems (i.e. where there's some routine that can be optimised by moving some of it into code). - Office SQL is a PITA. I gave up once I saw they don't implement COALESCE. It's spelled Nz in MS Access, but its semantics leave a little to be desired and it doesn't seem to exist in Excel for some strange reason. I was thinking about opening another ODBC connection and using dblink to import data from Excel to pg... but somehow it doesn't look it is going to really improve the procedure of importing data from a csv. Depends on the problem you're trying to solve; but I've had code uploading binary blobs into large objects into PG and then writing a set of functions that pull the data out of these blobs into nice relational tables. My files were somewhat more structured than Excel files so it may not help much. I mean... someone doesn't do it properly (eg. some kind of lock/whatever on the Excel file) people won't be able to understand the problem. Saving the Excel file to csv seems something could be handled easier by the people that will do the job. Doing that in code in Excel would allow you to throw up better errors earlier. You have no idea how much pain you are letting yourself into. ;) Right now it looks as a lesser pain than eg. trying to use aggregates and grouping on Excel. Grouping and aggregates are spelled pivot tables in Excel and they work well for a single level, they don't scale to more than one level though and they require considerably more manual housekeeping than SQL. As always, it's about picking the right tools for the job! -- 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] Need help
On Mon, May 18, 2009 at 6:11 PM, Albe Laurenz laurenz.a...@wien.gv.atwrote: I'm still not sure what that means, but if you have the contents of the database, but the database itself is gone without a trace, then what else can you do but create a new database and fill it anew? That`s what i am asking about. :) Fill it anew? That means, i have to insert all 2000 data again?? Is there any other option to restore all data there? -- Best, Zico
Re: [GENERAL] referring to calculated column in sub select
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
Re: [GENERAL] Need help
On Mon, May 18, 2009 at 09:21:41PM +0430, Zico wrote: On Mon, May 18, 2009 at 6:11 PM, Albe Laurenz laurenz.a...@wien.gv.atwrote: then what else can you do but create a new database and fill it anew? That`s what i am asking about. :) Fill it anew? That means, i have to insert all 2000 data again?? Is there any other option to restore all data there? You need to get the contents of the database from somewhere; the obvious choices seem to be an existing copy of the PG database directory, a backup of the database, or the original data. Your previous messages seem to indicate that you do not have either of the first to items and so the only choice remaining would be to use the original data. -- 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] Daylight saving time question
- Original Message - From: John R Pierce pie...@hogranch.com To: Bayless Kirtley bk...@cox.net; PostgreSQL pgsql-general@postgresql.org Sent: Sunday, May 17, 2009 10:19 PM Subject: Re: [GENERAL] Daylight saving time question Bayless Kirtley wrote: How can I tell PostgreSQL to use daylight saving time when applicable? Times returned by the database are one hour behind. it uses your client's specified local time zone to determine whether or not DST is in effect. SET TIME ZONE 'America/New York'; or SET TIME ZONE 'PST8PDT'; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general For some reason I can't seem to make it work. I have tried setting the timezone in postgresql.conf as timezone = 'America/Chicago' and timezone = 'CST6CDT' both of which still returned one hour behind. I also tried both of your suggestions as SQL statements right after establishing a database connection and still get the same wrong time. I have a Java application on Windows XP PRO and the way I am getting the time is Select CURRENT_TIME. Is there something I am missing or is there another way I should be getting the time? Thanks again -- 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] Need help
Zico wrote: On Mon, May 18, 2009 at 6:11 PM, Albe Laurenz laurenz.a...@wien.gv.at mailto:laurenz.a...@wien.gv.at wrote: I'm still not sure what that means, but if you have the contents of the database, but the database itself is gone without a trace, then what else can you do but create a new database and fill it anew? That`s what i am asking about. :) Fill it anew? That means, i have to insert all 2000 data again?? Is there any other option to restore all data there? we still don't understand exactly what you have to work from Do you have a backup of the Postgres data directory, which typically is something like C:\Program Files\PostgreSQL\8.2\data on a Windows system? If so, you can install the same version of postgres, then STOP it, copy the 'data' directory into the proper place, and restart the server, and it should see your databases all intact. You might have to mess around a bit with file permissions, windows makes that kind of sketchy. if you don't have a backup of this data directory, and you've said you don't have any pg_dumpall type backups made prior to the system crash, then, what exactly do you expect to be able to restore your data from? -- 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] Need help
On Mon, May 18, 2009 at 06:15:28PM +0100, Sam Mason wrote: You need to get the contents of the database from somewhere; the obvious choices seem to be an existing copy of the PG database directory, a backup of the database, or the original data. Your previous messages seem to indicate that you do not have either of the first to items and bah, what's happening to my spelling! this should obviously be two. so the only choice remaining would be to use the original data. -- 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] Daylight saving time question
Bayless Kirtley bk...@cox.net writes: For some reason I can't seem to make it work. I have tried setting the timezone in postgresql.conf as timezone = 'America/Chicago' and timezone = 'CST6CDT' both of which still returned one hour behind. I also tried both of your suggestions as SQL statements right after establishing a database connection and still get the same wrong time. I have a Java application on Windows XP PRO and the way I am getting the time is Select CURRENT_TIME. Is there something I am missing or is there another way I should be getting the time? Are you sure the system's time is actually set correctly on the server machine? Seems like confusion between standard and daylight time in setting the server's clock might be the underlying issue here. Another theory is that the database is perfectly fine but there's something wacko happening on the Java side. Have you tried running select current_time from some other application, like psql? (In that connection I note that select current_time only gives time of day not a full timestamp, so I'd not exactly be surprised if it does confuse Java. select current_timestamp produces a much less ambiguous result.) 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] Daylight saving time question
On Mon, May 18, 2009 at 11:16 AM, Bayless Kirtley bk...@cox.net wrote: - Original Message - From: John R Pierce pie...@hogranch.com Bayless Kirtley wrote: How can I tell PostgreSQL to use daylight saving time when applicable? Times returned by the database are one hour behind. it uses your client's specified local time zone to determine whether or not DST is in effect. SET TIME ZONE 'America/New York'; or SET TIME ZONE 'PST8PDT'; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general For some reason I can't seem to make it work. I have tried setting the timezone in postgresql.conf as timezone = 'America/Chicago' and timezone = 'CST6CDT' both of which still returned one hour behind. I also tried both of your suggestions as SQL statements right after establishing a database connection and still get the same wrong time. It's not about what's set in postgresql.conf, it's what the client timezone is. If you connect from your client and issue show timezone; what do you get? I have a Java application on Windows XP PRO and the way I am getting the time is Select CURRENT_TIME. Is there something I am missing or is there another way I should be getting the time? Nope, sounds right. Again, what's the client application think the timezone is? -- 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] Daylight saving time question
Thanks Tom and Scott. You got me looking in the right direction. In this case the client and server are on the same machine (testing/development) and psql does return the right result. I tried all the possibilities from the java program, show timezone, select current_time and select current_timestamp. These were all JDBC queries. When I used result.getString(), the values looked right. When I used result.getTime(), they were off by one hour as if daylight saving were not in effect. Is this a flaw in the JDBC driver or is that the expected behavior? In either case I do now have a workaround but would like to know. Thanks again. - Original Message - From: Tom Lane t...@sss.pgh.pa.us To: Bayless Kirtley bk...@cox.net Cc: John R Pierce pie...@hogranch.com; PostgreSQL pgsql-general@postgresql.org Sent: Monday, May 18, 2009 12:26 PM Subject: Re: [GENERAL] Daylight saving time question Bayless Kirtley bk...@cox.net writes: For some reason I can't seem to make it work. I have tried setting the timezone in postgresql.conf as timezone = 'America/Chicago' and timezone = 'CST6CDT' both of which still returned one hour behind. I also tried both of your suggestions as SQL statements right after establishing a database connection and still get the same wrong time. I have a Java application on Windows XP PRO and the way I am getting the time is Select CURRENT_TIME. Is there something I am missing or is there another way I should be getting the time? Are you sure the system's time is actually set correctly on the server machine? Seems like confusion between standard and daylight time in setting the server's clock might be the underlying issue here. Another theory is that the database is perfectly fine but there's something wacko happening on the Java side. Have you tried running select current_time from some other application, like psql? (In that connection I note that select current_time only gives time of day not a full timestamp, so I'd not exactly be surprised if it does confuse Java. select current_timestamp produces a much less ambiguous result.) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help
On Mon, May 18, 2009 at 9:48 PM, John R Pierce pie...@hogranch.com wrote: we still don't understand exactly what you have to work from Do you have a backup of the Postgres data directory, No, I don`t have any data of Postgres data directory. which typically is something like C:\Program Files\PostgreSQL\8.2\data As far as i can remember, my postgre files were in /usr/share/postgresql/8.3 as i am using the Debian distro. if you don't have a backup of this data directory, and you've said you don't have any pg_dumpall type backups made prior to the system crash, then, what exactly do you expect to be able to restore your data from? 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!! :( -- Best, Zico
[GENERAL] Providing an alternative result when there is no result
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; I'd like to combine this into one step like this: SELECT CASE WHEN id is null THEN 'NO'::text ELSE 'YES'::text END FROM ...; But, this is not have SELECT's work, I suppose. The CASE is never encountered when there is no result, so in the NO case, NULL is returned. Any hints/tips? Is our original solution okay, or is there something we can do to improve things? Thanks, Joshua Berry -- 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
Hello look on GET DIAGNOSTIC statement or FOUND variable CREATE OR REPLACE FUNCTION foo() RETURNS boolean AS $$ BEGIN SELECT INTO temp_table ... RETURN found; END; $$ language plpgsql; regards Pavel Stehule 2009/5/18 Joshua Berry yob...@gmail.com: 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; I'd like to combine this into one step like this: SELECT CASE WHEN id is null THEN 'NO'::text ELSE 'YES'::text END FROM ...; But, this is not have SELECT's work, I suppose. The CASE is never encountered when there is no result, so in the NO case, NULL is returned. Any hints/tips? Is our original solution okay, or is there something we can do to improve things? Thanks, Joshua Berry -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 ... ; What might work is: SELECT EXISTS(subquery); As in: SELECT EXISTS( SELECT 1 WHERE true ); Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] Providing an alternative result when there is no result
On Mon, May 18, 2009 at 3:13 PM, Joshua Berry yob...@gmail.com wrote: Any hints/tips? Is our original solution okay, or is there something we can do to improve things? It seems as if you don't really care about the results of the query- just whether or not it returns any rows. In that case, why not something like: select (case when exists (select * from foo where...) then true else false end) as result; -- - David T. Wilson david.t.wil...@gmail.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] Providing an alternative result when there is no result
On Mon, 2009-05-18 at 15:13 -0400, 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? Presuming that you're not using the values in temp_table, I think you should be using PERFORM * WHERE ...; and then IF FOUND ... ELSE ... END IF; See here: http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html and also follow link to 38.5.5 . -Reece
Re: [GENERAL] Daylight saving time question
Bayless Kirtley bk...@cox.net 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 -- 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] Need help
On Mon, May 18, 2009 at 11:33:03PM +0430, Zico wrote: we still don't understand exactly what you have to work from Do you have a backup of the Postgres data directory, No, I don`t have any data of Postgres data directory. Well, in that case I would suggest to IMMEDIATELY STOP WRITING anything to the hard drive the data directory used to be on (in other words, unmount it). Then, mount it read-only and use low level tools to try and recover the data. If you are EXTREMELY lucky you'll find something. I don`t know, what should i do! First of all you need to provide detailed answers to what people ask you on this list. I know there are extremely knowledgeable people around here but they can only help if you provide ample and precise details. Because, i have only the softcopy of my data What is a softcopy ? Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Data in a table to a csv file
Hello, I have a small problem,I need to export data from a table into a csv file.For this i am using pqxx library to work on it.I can able to retrieve the data from the database but the only problem is I am unable to store it in a csv file.Can you guys please help me how to do that. Thanks, Aravind.
Re: [GENERAL] Data in a table to a csv file
I have a small problem,I need to export data from a table into a csv file.For this i am using pqxx library to work on it.I can able to retrieve the data from the database but the only problem is I am unable to store it in a csv file.Can you guys please help me how to do that. I'm no Postgresql expert, but I've used the builtin COPY SQL command. You can find the documentation on it here: http://www.postgresql.org/docs/8.3/interactive/sql-copy.html Note: 1. Using COPY in this way is that Postgresql will create the csv file on the server's filesystem, not on your client machine, if you use a different machine for server and client. 2. when you create the file, the directory that you create the file in must be writable by Postgres, I just always use /tmp as anyone can create files in that directory. 3. #2 is probably specific to UNIX environments like Linux or MacOS X. HTH, -Joshua Joshua Berry
[GENERAL] Commit visibility guarantees
Hello Everyone, I'm looking at an easy real-time application using PostgreSQL. Looking at the pg docs, I see lots of discussion about cases where MVCC may reflect different versions of the data to different connections. For example: http://www.postgresql.org/docs/8.1/static/mvcc.html while querying a database each transaction sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data Read Committed is the default isolation level ... a SELECT query sees only data committed before the query began; it never sees either uncommitted data or changes committed during query execution by concurrent transactions. (However, the SELECT does see the effects of previous updates executed within its own transaction, even though they are not yet committed.) In effect, a SELECT query sees a snapshot of the database as of the instant that that query begins to run. However, I don't actually see any statements giving guarantees about when the updated data _does_ become visible. 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? The db is in the default read committed mode. The select is being done on another long-running connection which has never done any updates, just selects within its implicit transaction. Maybe this is the statement I'm looking for: in Read Committed mode each new command starts with a new snapshot that includes all transactions committed up to that instant, subsequent commands in the same transaction will see the effects of the committed concurrent transaction. But this statement is just an aside when making a different point, and I see other statements like So the whole concept of now is somewhat ill-defined anyway. This is not normally a big problem if the client applications are isolated from each other, but if the clients can communicate via channels outside the database then serious confusion may ensue. And communication via outside channels is exactly what the app is doing. Thanks, - Marsh -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] array/function question
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
--
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] Data in a table to a csv file
Joshua Berry wrote: I have a small problem,I need to export data from a table into a csv file.For this i am using pqxx library to work on it.I can able to retrieve the data from the database but the only problem is I am unable to store it in a csv file.Can you guys please help me how to do that. I'm no Postgresql expert, but I've used the builtin COPY SQL command. You can find the documentation on it here: http://www.postgresql.org/docs/8.3/interactive/sql-copy.html Note: 1. Using COPY in this way is that Postgresql will create the csv file on the server's filesystem, not on your client machine, if you use a different machine for server and client. 2. when you create the file, the directory that you create the file in must be writable by Postgres, I just always use /tmp as anyone can create files in that directory. 3. #2 is probably specific to UNIX environments like Linux or MacOS X. You can have the copy routed to stdout on the client side. In psql (using an area-code/prefix table I have for example): copy (select npa,nxx,state from npanxx limit 10) to stdout csv header; Capture, (or reroute with \o in psql) the output as appropriate for your app. In this example, relevant file permissions are those of the client, not the server. Remove header if you want the data only or rename the headers with as... if you need (...npa as Area Code, nxx as Prefix...). Alternately use whatever csv support your client language supports (Python's csv module for example). Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Commit visibility guarantees
On Mon, May 18, 2009 at 04:38:36PM -0500, 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? Maybe this is the statement I'm looking for: in Read Committed mode each new command starts with a new snapshot that includes all transactions committed up to that instant, subsequent commands in the same transaction will see the effects of the committed concurrent transaction. For read committed that sounds like what I'd expect, row level locking buys you a bit more in implementation terms but just complicates the formal side as queries don't attempt to do any locking by default. I'm not aware of any formally defined semantics that PG tries to be a faithful implementation of---i.e. there may be bugs, but when they're fixed where are we aiming for. If somebody could come up with a nice set of inductive definitions I'd be interested in seeing what they implied as well. But this statement is just an aside when making a different point, and I see other statements like So the whole concept of now is somewhat ill-defined anyway. Not sure if it's quite as bad as that; transactional semantics go a long way to making large classes of problems simple. The interactions between two independent systems that both have transactional semantics get very awkward. Unbounded rollback being a term I remember, but can't remember when/why it applies. This is not normally a big problem if the client applications are isolated from each other, but if the clients can communicate via channels outside the database then serious confusion may ensue. And communication via outside channels is exactly what the app is doing. Yes, things get awkward when you start doing this. Is there anyway to keep things inside the database, using NOTIFY or somesuch? Could you define what you mean by real-time, do you mean the strict academic meaning or just that you want interactive things happening and it would be annoying if they were delayed by a few tens of milliseconds (as opposed to someone dieing because something got delayed by a millisecond). -- 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] Commit visibility guarantees
On Mon, May 18, 2009 at 4:53 PM, Ben Chobot be...@silentmedia.com wrote: On Mon, 18 May 2009, Marsh Ray wrote: Hello Everyone, I'm looking at an easy real-time application using PostgreSQL. As I understand real-time applications, PostgreSQL is inherintly unsuited for the task. There is absolutely no timing constraints on your queries, and large sets of working data can sometimes spill to disk, which incurs the obvious - but not always consistent - performance hit. Definitely true, but I don't think it's really the issue here. The app does have a hard real-time deadline, but the deadline is generally quite easy for such a system. Like any web app, there will be a hard deadline to meet before the browser times out, though the timeout value 60 or more seconds. There is a near-instantaneous context switch from the 'update' process to the 'select' process, and I am wondering if some behaviors change with that tight scheduling. - Marsh -- 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, May 18, 2009 at 05:18:06PM -0500, Marsh Ray wrote: On Mon, May 18, 2009 at 4:53 PM, Ben Chobot be...@silentmedia.com wrote: On Mon, 18 May 2009, Marsh Ray wrote: Hello Everyone, I'm looking at an easy real-time application using PostgreSQL. As I understand real-time applications, PostgreSQL is inherintly unsuited for the task. There is absolutely no timing constraints on your queries, and large sets of working data can sometimes spill to disk, which incurs the obvious - but not always consistent - performance hit. Definitely true, but I don't think it's really the issue here. The app does have a hard real-time deadline, but the deadline is generally quite easy for such a system. Like any web app, there will be a hard deadline to meet before the browser times out, though the timeout value 60 or more seconds. Even then it's not useful to class it as real-time; nothing bad happens if you don't get a response before timeout the user just gets an error message. Real-time applies when if you don't get a response the plane crashes or a heart stops because the pacemaker hasn't put out a signal in time. -- 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
[GENERAL] Where is tsearch2.sql
Hi, 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. Where is it??? Thanks, Howard Cole www.selestial.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] Commit visibility guarantees
On Mon, May 18, 2009 at 5:24 PM, Sam Mason s...@samason.me.uk wrote: Even then it's not useful to class it as real-time; nothing bad happens if you don't get a response before timeout the user just gets an error message. Real-time applies when if you don't get a response the plane crashes or a heart stops because the pacemaker hasn't put out a signal in time. Think of a network router: it is neither a pacemaker nor an airplane but is definitely a real-time system. If it cannot complete its processing on time, not only is it useless, it may actually bring down significant numbers of other systems. - Marsh -- 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, May 18, 2009 at 5:14 PM, Sam Mason s...@samason.me.uk wrote: On Mon, May 18, 2009 at 04:38:36PM -0500, 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? Maybe this is the statement I'm looking for: in Read Committed mode each new command starts with a new snapshot that includes all transactions committed up to that instant, subsequent commands in the same transaction will see the effects of the committed concurrent transaction. For read committed that sounds like what I'd expect, row level locking buys you a bit more in implementation terms but just complicates the formal side as queries don't attempt to do any locking by default. I'm not aware of any formally defined semantics that PG tries to be a faithful implementation of---i.e. there may be bugs, but when they're fixed where are we aiming for. If somebody could come up with a nice set of inductive definitions I'd be interested in seeing what they implied as well. Do you know if this kind of concurrency test is included in pg's regression tests? But this statement is just an aside when making a different point, and I see other statements like So the whole concept of now is somewhat ill-defined anyway. Not sure if it's quite as bad as that; transactional semantics go a long way to making large classes of problems simple. The interactions between two independent systems that both have transactional semantics get very awkward. Unbounded rollback being a term I remember, but can't remember when/why it applies. At some point, a committed update has got to show up in other connections' selects or users would obviously complain. However, is the lag guaranteed to be zero? This is not normally a big problem if the client applications are isolated from each other, but if the clients can communicate via channels outside the database then serious confusion may ensue. And communication via outside channels is exactly what the app is doing. Yes, things get awkward when you start doing this. Yep, awkward city. I'm just trying to figure out what guarantees I do get out of pg in order to analyze it going forward. Is there anyway to keep things inside the database, using NOTIFY or somesuch? Unfortunately no, the db really is between external actors that also have their own kernel-object-fast signaling mechanism. The data currently being passed via the db could be duplicated over this side channel, but it would be far more interesting to learn if a basic assumption was wrong. Could you define what you mean by real-time, do you mean the strict academic meaning or just that you want interactive things happening and it would be annoying if they were delayed by a few tens of milliseconds (as opposed to someone dieing because something got delayed by a millisecond). It is real-time in the academic definition, but most deadlines are measured in seconds. Not too different than a web app really. - Marsh -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] need to do force uninstall of postgres on windows vista 64
Hi, I'm having some pain getting Postgres 8.3 installed on windows vista 64. The first time I tried to install it, I was using the .msi version, which eventually hit permissions problems, and when I aborted the install, the uninstall did not seem to take. Then I tried the .exe version of 8.3, and I ran into the problem documented here relating to Date/Time settings flip-flopping between 64-bit integers and floating point numbers: http://www.nabble.com/8.3.7-Windows-Update-Error-td22707651.html The workaround there is to do a clean install of 8.3, but my original install didn't leave me an uninstaller, so I decided to upgrade to 8.4. 8.4 locked up at first, but then I rebooted my PC and was able to do a complete install and uninstall of 8.4, which, if nothing else, convinced me that there's some hope of getting through the install pain. Unfortunately, 8.4 does not seem to have PostGIS support yet, which is the major reason I'm trying to go to Postgres in the first place. So I need to go back to 8.3. So, long story short, I need a way to force the uninstall of 8.3. Has anybody had to do this before? I can try things like forcibly removing the directory, hacking the registry, etc., but I would like to work off of some documentation if possible. Thanks, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Commit visibility guarantees
Marsh Ray marsh5...@gmail.com writes: 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. The point that the remark about ill-defined behavior is trying to make is that the application could try to compare the results of queries done using different snapshots, and those results might be different. 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] Commit visibility guarantees
On Mon, May 18, 2009 at 6:20 PM, Tom Lane t...@sss.pgh.pa.us wrote: Marsh Ray marsh5...@gmail.com writes: 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... - Marsh -- 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ó:
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.
I think this is much easier to write in PL/Perl than PL/pgSQL. Trivial
in fact. Your example is flawed though (three falses instead of two) ...
I think it looks like this:
create or replace function is_element_present(int[], int[]) returns bool[]
language plperl as $$
$a = shift;
$b = shift;
if ($a =~ /{(.*)}/) {
@a = split /,/, $1
}
if ($b =~ /{(.*)}/) {
@b = split /,/, $1
}
for my $k (@b) {
$h{$k} = 1;
}
@c = map { if (defined $h{$_}) { 1 } else { 0 } } @a;
return \...@c;
$$;
Hmm, well, the fact that PL/Perl passes arrays as string kinda sucks --
fixing that takes half the code of the function!
alvherre=# select is_element_present('{1,2,3,4,7}', '{1,4,8,9}');
is_element_present
{t,f,f,t,f}
(1 fila)
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
--
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 escribió: On Mon, May 18, 2009 at 6:20 PM, Tom Lane t...@sss.pgh.pa.us wrote: Marsh Ray marsh5...@gmail.com writes: 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. On a serializable transaction all queries will use the same snapshot taken when the first query is executed. Otherwise (read committed), a new query always gets a fresh one. (Old snapshots are also used for stuff like cursors that remain open, but that's not the case here.) -- 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
hi,
you should use something similar to 'merge sort'
but only if your input is sorted (m_bx expects this)
if your subjects (numbers) are not going beyond a certain limit eg(65535)
take up an array and filter
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 ;)
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 ila 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]a[i]) then
j=j+1;
else
i=i+1;
end if;
end loop;
return res;
end;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE
COST 100;
select m_bx('{1,2,4,5}','{1,5,6}');
Joshua Berry wrote:
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is this a bug or a feature? Column visibility in subquery from outer query
Thank you for clearing that up. Regards, Paolo Saul This behavior is required by the SQL standard. It's called an outer reference. regards, tom lane
Re: [GENERAL] array/function question
2009/5/18 Joshua Berry yob...@gmail.com:
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 }
hello
try to SQL language
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
Time: 1,846 ms
postgres=# select xx(array[1,2,3,4,7], array[1,4,8,9]);
xx
-
{t,f,f,t,f}
(1 row)
if you know, so input are distinct and sorted, then you could to use function:
postgres=# create or replace function xy(anyarray, anyarray) returns
bool[] as $$
select array(select y is not null from unnest($1) g1(x) left join
unnest($2) g2(y) on x = y order by x);
$$ language sql immutable;
CREATE FUNCTION
Time: 2,666 ms
postgres=# select xx(array[1,2,3,4,7], array[1,4,8,9]); xx
-
{t,f,f,t,f}
(1 row)
regards
Pavel Stehule
regards
Pavel Stehule
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
--
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
