Re: [GENERAL] Moving the database from winxp to linux
Tomas Lanczos napisał(a): Thanks. No complications due the move to the higher version (8.2)? Tomas Per usual remarks about upgrading found in installation instructions of every release, you will need to use pg_dump that comes with the new version to connect to the old database and run your dump, to make sure the dump is compatible with the new version. Consult the -i option of pg_dump. Just make a try having the new server ready. I guess you are in a quite comfort situation because you seem to be able to run the two machines simultaneously for a while. Be sure to use 8.2.1, just released. Irek. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Foreign Key Identification
Thank You Guys, For your valuable suggestions. Out of the suggestion to investigate in to PG_depane was cumbersome , yes there is a view in information schema called information_schema.referential_constraints which gives same details in terms of foreign keys and primary keys and is usefull if u have strict nameing convention for foreign key and primary key in order to arive at table name.. Reece pgutil is excellent to get the all required information on foreign key tables and primary key tables. With Regards Ashish Karalkar --- Reece Hart [EMAIL PROTECTED] wrote: From: Ashish Karalkar ashish_karalkar ( at ) yahoo ( dot ) com * To: pgsql-general ( at ) postgresql ( dot ) org * Subject: Foreign Key Identification * Date: Wed, 10 Jan 2007 08:23:28 -0800 (PST) Hello All, Is there any means to list out the foreign key tables which are liked with a primary key? What i want to do is something as follows: ... select tablename from ? where Foreign key = TableAPK and this should list me I needed to do something similar. I wrote some views to do this and packaged 'em up as something I call pgutils. See: http://archives.postgresql.org/pgsql-general/2006-11/msg00752.php http://harts.net/reece/pgutils/ -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ./universe -G 6.672e-11 -e 1.602e-19 -protonmass 1.673e-27 -uspres bush kernel warning: universe consuming too many resources. Killing. universe killed due to catastrophic leadership. Try -uspres carter. Yahoo! Music Unlimited Access over 1 million songs. http://music.yahoo.com/unlimited ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Knowing the length(convert(username using windows_1251_to_utf8))
Hello PostgreSQL users! I have this data stored in WIN1251 encoding, which is being fetched by a libpq application I'm developing: phpbb= show client_encoding; - WIN1251 (1 row) phpbb= \d phpbb_users; username | character varying(25) | not null default ''::character phpbb= select username, length(username), length(convert(username using windows_1251_to_utf8)) from phpbb_users where user_id=224; username | length | length -++ Лукашенко И. В. | 15 | 26 (1 row) My problem is that I need the username in the utf8 encoding. So I use the convert(username using windows_1251_to_utf8) which works fine except one thing: Is there please a way to know the length of the utf8 data? (I'm using a fixed char array in my C program) I was using char name[25 + 1] initially, but now I see that it isn't sufficient. Should I use char name[25 * 2 + 1] ? How do you usually handle such cases? Thank you for any advices Alex -- http://preferans.de ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Knowing the length(convert(username using windows_1251_to_utf8))
And additional question please: Can I still be sure that the data returned in the convert(username using windows_1251_to_utf8) column will be 0-terminated or should I fetch the data length using PQgetlength and maintain that value in my C-program? Thank you Alex On 1/11/07, Alexander Farber [EMAIL PROTECTED] wrote: phpbb= show client_encoding; - WIN1251 (1 row) phpbb= \d phpbb_users; username | character varying(25) | not null default ''::character phpbb= select username, length(username), length(convert(username using windows_1251_to_utf8)) from phpbb_users where user_id=224; username | length | length -++ Лукашенко И. В. | 15 | 26 (1 row) -- http://preferans.de ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Postgres Replication
dcrespo wrote: Good question. The only concern that I have is the date of the last version (2005-3-7). You will find that their website has not been updated for a while. If you look in pgfoundry you will find that they have releases as recent as a few days ago. The different 1.x versions relate to a different postgres version (1.7.x is 8.2) (1.5.x is 8.1) (1.3.x is 8.0). http://pgfoundry.org/projects/pgcluster Do you or anybody know if this software (PGCluster) is stable and works fine? Please, give information on how it fits your needs. I haven't used it myself, just been looking around out of curiosity. Thank you! Daniel km wrote: On Tue, Jan 09, 2007 at 12:17:20PM -0600, Scott Marlowe wrote: Has anybody researched on this that can point me in the right direction? You could use possibly use pgpool as long as its caveats aren't a show stopper (can't insert with random, individual inserts with things like now() might be a little different, insert order might not be the same on both machines, etc... I haven't used daffodil, but have heard of it. There's also c-jdbc and a few others. what abt pgcluster ? how does it fare with SlonyI ? regards, KM ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PG compilation
km wrote: Hi, I would like to know if there is a way to pass an argument to ./configure to consider compiling with a specific python version ? coz i have many python versions in the system .I presume that configure would check for the /usr/bin/python alone, but what if i want /usr/bin/python2.5 to be compiled in ? Have a look at ./configure --help -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Recording insert, updates, and deletes
Hi, I turned on the stats_row_level in the postgresql.conf file and now the the calls to the stats functions work. I want to get the inserted, updated, and deleted numbers on a given database, so i have written a query to do so: SELECT sum(pg_stat_get_tuples_inserted(c.oid)) AS inserted, sum(pg_stat_get_tuples_updated(c.oid)) AS updated, sum(pg_stat_get_tuples_deleted(c.oid)) AS deleted FROM pg_class c, information_schema.tables i WHERE i.table_catalog = 'testdb' AND i.table_schema= 'public' AND i.table_name = c.relname I had to use the information schema as i could not figure out a way to fetch the tables of a particular database using only pg_* tables. What i am really now concerned is reliability, is it possible that the stats can be incorrect ? and are they never reset ?. Also does using row level stats have a serious effect on the performance ? Cheers, Andy On 10/01/07, Scott Marlowe [EMAIL PROTECTED] wrote: On Wed, 2007-01-10 at 12:37, Andy Dale wrote: Hi Brad, If i have to create a separate slony replication set, then i cannot do it this way (i cannot and do not want to have a master-slave(s) architecture) Andy On 10/01/07, Brad Nicholson [EMAIL PROTECTED] wrote: On Wed, 2007-01-10 at 16:51 +0100, Andy Dale wrote: If anyone can help or offer advice on how to achieve my objective it would be greatly appreciated. Slony log shipping will do this I've lost the OP, but look in the contrib/spi directory for something designed to do auditing of inserts / deletes etc...
Re: [GENERAL] Recording insert, updates, and deletes
Andy Dale wrote: Hi, I turned on the stats_row_level in the postgresql.conf file and now the the calls to the stats functions work. I want to get the inserted, updated, and deleted numbers on a given database, so i have written a query to do so: SELECT sum(pg_stat_get_tuples_inserted(c.oid)) AS inserted, sum(pg_stat_get_tuples_updated(c.oid)) AS updated, sum(pg_stat_get_tuples_deleted(c.oid)) AS deleted FROM pg_class c, information_schema.tables i WHERE i.table_catalog = 'testdb' AND i.table_schema= 'public' AND i.table_name = c.relname I had to use the information schema as i could not figure out a way to fetch the tables of a particular database using only pg_* tables. It's the pg_class table you'll want to start with. If you start psql with -E and then do \dt you'll see the queries it uses. What i am really now concerned is reliability, is it possible that the stats can be incorrect ? and are they never reset ?. Also does using row level stats have a serious effect on the performance ? Well, I'm not sure about incorrect. AFAIK the stats gatherer is lossy, so there's not a 100% guarantee that every read/write is measured. Performance shouldn't be an issue unless you're already pushing the limits of your hardware. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Cluster all tables in database to PK index
Besides writing a script that looks through the DDL of all tables, and CLUSTERs all tables with PK constraints, is there a quicker way? Thanks. -- Regards, Hannes Dorbath ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Knowing the length(convert(username using windows_1251_to_utf8))
On Thu, Jan 11, 2007 at 10:19:38AM +0100, Alexander Farber wrote: Hello PostgreSQL users! I have this data stored in WIN1251 encoding, which is being fetched by a libpq application I'm developing: snip phpbb= select username, length(username), length(convert(username using windows_1251_to_utf8)) from phpbb_users where user_id=224; username | length | length -++ ? ?. ?. | 15 | 26 (1 row) My problem is that I need the username in the utf8 encoding. So I use the convert(username using windows_1251_to_utf8) which works fine except one thing: If you need the string in UTF-8, why not just set the client_encoding to utf8 and then the server will only send you strings in utf8, not conversion necessary. Is there please a way to know the length of the utf8 data? (I'm using a fixed char array in my C program) UTF-8 always variable length, I think up to 4 bytes per character. Maybe you should n't be using a fixed-length array? How do you usually handle such cases? Variable length arrays. In your next email you ask: Can I still be sure that the data returned in the convert(username using windows_1251_to_utf8) column will be 0-terminated or should I fetch the data length using PQgetlength and maintain that value in my C-program? In the client end (as long you're not doing binary transfers) the strings are always null terminated. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Recording insert, updates, and deletes
Sorry for being stupid, you can select the table info from the pg_class table, so i can ignore the information schema. If the stats collector is 'lossy ' i will not be able to use it, can anyone confirm that it is ? So maybe my best option is to write a simple trigger that just increments a counter (value in a separate table) after an insert/update/delete and then add this trigger to each table i want to record the stats for. Would this new approach work (i.e. be lossless) ? Cheers, Andy On 11/01/07, Richard Huxton dev@archonet.com wrote: Andy Dale wrote: Hi, I turned on the stats_row_level in the postgresql.conf file and now the the calls to the stats functions work. I want to get the inserted, updated, and deleted numbers on a given database, so i have written a query to do so: SELECT sum(pg_stat_get_tuples_inserted(c.oid)) AS inserted, sum(pg_stat_get_tuples_updated(c.oid)) AS updated, sum(pg_stat_get_tuples_deleted(c.oid)) AS deleted FROM pg_class c, information_schema.tables i WHERE i.table_catalog = 'testdb' AND i.table_schema= 'public' AND i.table_name = c.relname I had to use the information schema as i could not figure out a way to fetch the tables of a particular database using only pg_* tables. It's the pg_class table you'll want to start with. If you start psql with -E and then do \dt you'll see the queries it uses. What i am really now concerned is reliability, is it possible that the stats can be incorrect ? and are they never reset ?. Also does using row level stats have a serious effect on the performance ? Well, I'm not sure about incorrect. AFAIK the stats gatherer is lossy, so there's not a 100% guarantee that every read/write is measured. Performance shouldn't be an issue unless you're already pushing the limits of your hardware. -- Richard Huxton Archonet Ltd
Re: [GENERAL] Recording insert, updates, and deletes
On Thu, Jan 11, 2007 at 11:46:17AM +0100, Andy Dale wrote: If the stats collector is 'lossy ' i will not be able to use it, can anyone confirm that it is ? So maybe my best option is to write a simple trigger that just increments a counter (value in a separate table) after an insert/update/delete and then add this trigger to each table i want to record the stats for. Would this new approach work (i.e. be lossless) ? Yes, the stats collector is designed so that if the server is very busy, it sacrifices accuracy for speed. It's designed to be minimal impact so that it can be turned on without slowing down your system. You on the other hand want accuracy over speed, and so the stats collector is not what you want. Some triggers will do it fine. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
[GENERAL] Optimize expresiions.
Hello, Given a table: create table atable ( item integer; ); and a view: create view aview as select item, very_expensive_function(item) as exp, cheap_function(item) as cheap from atable; Now the query: select item from aview where exp 0 and cheap 0; will lead to a sequential scan on atable with filter: very_expensive_function(item) 0 and cheap_function(item) 0 The query would run much faster with the filter reordered. Is there a way to tell the planner/optimizer that certain functions are more expensive than others, and should be postponed in lazy evaluation ? Or is there a hook in the system that would allow me too look at and maybe reorder expressions before they are executed ? Cheers, Han Holl ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PG compilation
I would like to know if there is a way to pass an argument to ./configure to consider compiling with a specific python version ? coz i have many python versions in the system .I presume that configure would check for the /usr/bin/python alone, but what if i want /usr/bin/python2.5 to be compiled in ? Have a look at ./configure --help ya had looked at '--with-python' option but that is where my question arises - it looks at default python (/usr/bin/python) and not /usr/local/bin/python2.5 which i need for PL/Python functionality in PG 8.2.1 one solution is to soft link the /usr/bin/python to /usr/local/bin/python2.5 but i donot want to change the default python on the system as some other programs depend on it. so i am looking for a flexibility in configuring PG that it created python modules with python2.5 only. any ideas ? regards, KM ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PG compilation
km wrote: I would like to know if there is a way to pass an argument to ./configure to consider compiling with a specific python version ? configure PYTHON=/usr/bin/python2.5 -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Optimize expresiions.
[EMAIL PROTECTED] wrote: Now the query: select item from aview where exp 0 and cheap 0; will lead to a sequential scan on atable with filter: very_expensive_function(item) 0 and cheap_function(item) 0 The query would run much faster with the filter reordered. Is there a way to tell the planner/optimizer that certain functions are more expensive than others, and should be postponed in lazy evaluation ? Or is there a hook in the system that would allow me too look at and maybe reorder expressions before they are executed ? Not really. Perhaps look into using a functional/expressional index and see if that does the job for you. http://www.postgresql.org/docs/8.2/static/indexes-expressional.html http://www.postgresql.org/docs/8.2/static/sql-createfunction.html -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Knowing the length(convert(username using windows_1251_to_utf8))
Hi Martijn, On 1/11/07, Martijn van Oosterhout kleptog@svana.org wrote: If you need the string in UTF-8, why not just set the client_encoding to utf8 and then the server will only send you strings in utf8, not conversion necessary. actually you are right, because I need all my data in UTF8 anyway (for a web flash client). So I've followed your advice and added: PQsetClientEncoding(conn, UTF8) and now my program works same, but without that convert(). Is there please a way to know the length of the utf8 data? (I'm using a fixed char array in my C program) UTF-8 always variable length, I think up to 4 bytes per character. Maybe you should n't be using a fixed-length array? Ok I'll go for the 4 times bigger fixed array for now, because I'd like to keep my webchat-like app quick. In your next email you ask: Can I still be sure that the data returned in the convert(username using windows_1251_to_utf8) column will be 0-terminated or should I fetch the data length using PQgetlength and maintain that value in my C-program? In the client end (as long you're not doing binary transfers) the strings are always null terminated. May I ask you an off-topic question? I've read several docs on Unicode, but they are difficult to understand. Do you think that an UTF8 string will ever have a 0 byte inside of it? Or is it safe to continue using strlen/strlcpy/strcmp on the UTF8 values I'll be fetching from my database? Regards Alex PS: Using postgresql-server-8.1.4 on OpenBSD 4.0-stable -- http://preferans.de ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] TRIGGER BEFORE INSERT
Hi!
I'm re-posting this message again in hope someone would have a look at
the case again. .. it's pending.
In postgres v7.2 I had a trigger function launched BEFORE INSERT, which
did everything I needed (like an UPDATE of other table inside of that
trigger function, and adjustment of the INSERTed ROW)
Currently (as of postgress v8.1.4) I have to put that function OUTSIDE
of a trigger and to achieve that same functionality, in addition to the
original INSERT to the original table, I have to:
1. make a separate SELECT on that table.
2. make a separate UPDATE on that table.
Does anyone have any idea how to 'optimise' that? Like folding-up the
three statements I need for this to work in v8.1.4 back to (or closer
to) the initial single statement?
I fear I lack the necesary SQL experience to optimise (I feel like lucky
to have a workaround).
Any help apreciated.
-R
On Tue, 2007-01-09 at 18:41 +0100, Rafal Pietrak wrote:
On Tue, 2007-01-09 at 10:44 -0500, Tom Lane wrote:
Rafal Pietrak [EMAIL PROTECTED] writes:
1. either the new value of test_days.dnia as already present in the
NEW row, is not visible to UPDATE test_utarg sub-statement of the same
transaction. But earlier versions of Postgres did allow for that
visibility.
2. or the constrainets in earlier postgres were checked on trigger
transaction COMMIT, not along the way; so the constraint violation
didn't occure then.
Current versions of PG check foreign keys at the end of each
insert/update/delete statement, so your before-insert trigger is in fact
erroneous: the referenced key does not yet exist in the target table.
I think 7.2 did constraint checking only when the entire interactive
command finished, but there were enough cases where that was wrong
that we changed it.
Consider declaring the foreign-key constraint as DEFERRED.
No luck here.
I've changed the trigger function to have triggers deferred, like the
following:
database=# CREATE OR REPLACE FUNCTION prado() RETURNS trigger AS $$
DECLARE wydano INTEGER; BEGIN SET CONSTRAINTS ALL DEFERRED ; UPDATE
test_utarg SET dnia=new.id WHERE tm BETWEEN new.dnia AND new.dnia
+'1day'::interval; GET DIAGNOSTICS wydano := ROW_COUNT; new.total :=
wydano; RETURN new; END; $$ LANGUAGE plpgsql;
and the results are still the same:
database=# INSERT INTO test_days (dnia) VALUES ('2007-01-06');
ERROR: insert or update on table test_utarg violates foreign key
constraint test_utarg_dnia_fkey
DETAIL: Key (dnia)=(3) is not present in table test_days.
CONTEXT: SQL statement UPDATE test_utarg SET dnia= $1 WHERE tm
BETWEEN $2 AND $3 +'1day'::interval
PL/pgSQL function prado line 1 at SQL statement
But I've never before used a deferred constraints - so may be I haven't
set it up correctly, in the above definition. Have I?
But actually, I've found a workaround: I've encapsulated the above
functionality inside of a function, which:
1. does an INSERT
2. subsequently does a SELECT of what i've just inserted (currently I'm
stuck with postgres v8.1.4 - so I cannot use INSERT ... RETURNING).
3. then I UPDATE the logtable
4. then I UPDATE the record INSERTED in step (1).
Originally, I had this functionality in a single TRIGGER BEFORE
function (OK, it fired UPDATE within - but I had the 'fresh' ROW of data
from step (1) all along with me, inside of that trigger function - no
need to SELECT/UPDATE it in separate statements).
So I get a performance panelty against my original schema.
Is there a way to optimise?
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Optimize expresiions.
On Thursday 11 January 2007 12:34, Richard Huxton wrote: The query would run much faster with the filter reordered. Is there a way to tell the planner/optimizer that certain functions are more expensive than others, and should be postponed in lazy evaluation ? Or is there a hook in the system that would allow me too look at and maybe reorder expressions before they are executed ? Not really. Perhaps look into using a functional/expressional index and see if that does the job for you. Oh well, pity. Thanks anyway. In my case, indexes don't help. Do you know if functionality like this has ever be considered ? I seem to encounter many queries where the order in the where clause matters, (but then we have a kind of weird database). Cheers, Han Holl ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Knowing the length(convert(username using windows_1251_to_utf8))
On Thu, Jan 11, 2007 at 12:37:32PM +0100, Alexander Farber wrote: May I ask you an off-topic question? I've read several docs on Unicode, but they are difficult to understand. Have you read the Unicode FAQ? http://www.cl.cam.ac.uk/~mgk25/unicode.html Do you think that an UTF8 string will ever have a 0 byte inside of it? Or is it safe to continue using strlen/strlcpy/strcmp on the UTF8 values I'll be fetching from my database? The answer to your questions are no and yes respectivly. See the FAQ. That is also one of the reasons why Linux/Unix went for utf-8, because it required minimal changes to programs (and in particular, the C library). Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Optimize expresiions.
How about this? select item, very_expensive_function(item) as exp, cheap from ( Select item, cheap_function(item) as cheap From atable where cheap_function(item) 0 ) sub where very_expensive_function(item) 0 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Thursday, January 11, 2007 4:59 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Optimize expresiions. Hello, Given a table: create table atable ( item integer; ); and a view: create view aview as select item, very_expensive_function(item) as exp, cheap_function(item) as cheap from atable; Now the query: select item from aview where exp 0 and cheap 0; will lead to a sequential scan on atable with filter: very_expensive_function(item) 0 and cheap_function(item) 0 The query would run much faster with the filter reordered. Is there a way to tell the planner/optimizer that certain functions are more expensive than others, and should be postponed in lazy evaluation ? Or is there a hook in the system that would allow me too look at and maybe reorder expressions before they are executed ? Cheers, Han Holl ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] TRIGGER BEFORE INSERT
On Thu, Jan 11, 2007 at 01:01:24PM +0100, Rafal Pietrak wrote: Hi! I'm re-posting this message again in hope someone would have a look at the case again. .. it's pending. Well, I can't help with the details because I can't see what you're trying to do, but I'm fairly sure you can't change te deferred state of triggers after the transaction has started. AIUI you also have to make the foreign key constraint deferrable otherwise you can't defer it in any case. This is incidently what Tom suggested. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] TRIGGER BEFORE INSERT
Rafal Pietrak wrote: Hi! I'm re-posting this message again in hope someone would have a look at the case again. .. it's pending. You were given a solution; defer the foreign key constraint. Alternatively, you may want to re-think your trigger function so that it does things in the right order. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Problems With VIEWS
Once again, thanks for the help.
OK, so I did the Adam's suggestion: SELECT * FROM pg_rules
and got the following returned:
apt=# select * from pg_rules;
schemaname | tablename | rulename|
definition
+-+---+-
pg_catalog | pg_settings | pg_settings_u |
CREATE RULE pg_settings_u AS ON UPDATE TO pg_settings WHERE (new.name =
old.name) DO SELECT set_config(old.name, new
.setting, false) AS set_config;
pg_catalog | pg_settings | pg_settings_n |
CREATE RULE pg_settings_n AS ON UPDATE TO pg_settings DO INSTEAD NOTHING;
(2 rows)
and then Richard's for the age_information VIEW specifically:
apt=# \d elementdata.age_information
View elementdata.age_information
Column | Type| Modifiers
-+---+---
elementid | bigint|
projectname | character varying |
original_date | date |
age_yrs | double precision |
upgrade_date| date |
upgrade_age_yrs | double precision |
View definition:
SELECT data_age_information.elementid, data_age_information.projectname,
data_a
ge_information.original_date, (date_part('year'::text,
age(data_age_information.
original_date::timestamp with time zone)) * 12::double precision +
date_part('mo
nth'::text, age(data_age_information.original_date::timestamp with time
zone)))
/ 12::double precision AS age_yrs, data_age_information.upgrade_date,
(date_part
('year'::text, age(data_age_information.upgrade_date::timestamp with time
zone))
* 12::double precision + date_part('month'::text,
age(data_age_information.upgr
ade_date::timestamp with time zone))) / 12::double precision AS
upgrade_age_yrs
FROM elementdata.data_age_information;
Obviously, there are no RULES associated with this VIEW, or any other view
that I have created in my db (all of which have at least two, most
three -INSERT, DELETE, UPDATE- RULES associated with them...)
So, herein probably lies my problem with the Cannont insert into a view
error I'm getting anytime I'm attempting to access it from my program -
there are no rules set up for them, right? So, if I manually enter them for
each VIEW and do a BACKUP and then RESTORE, should they still be attached to
each of the VIEWS? I would think CASEStudio would have this capabilty to
export these, but at this point I need to get our application and the VIEWS
working and if I need to take the time to enter each RULE for the VIEWS
manually, so be it.
Thoughts and feedback, as always, are very much welcomed!
Thanks again,
-Jeanna
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Richard Broersma
Jr
Sent: Wednesday, January 10, 2007 6:14 PM
To: Adam Rich; 'Jeanna Geier'; 'pgsql-general'
Subject: Re: [GENERAL] Problems With VIEWS
also,
if you open up psql, you can type \d view_name
and it will tell you everything about the view.
--- Adam Rich [EMAIL PROTECTED] wrote:
Select * from pg_rules ?
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Jeanna Geier
Sent: Wednesday, January 10, 2007 5:43 PM
To: Richard Broersma Jr; pgsql-general
Subject: Re: [GENERAL] Problems With VIEWS
I guess I have a general question regarding Postgres and RULES --
relative
newbie here, especially with these 'custom' functions -- where/how can
you
view the RULES in Postgres?
I use CaseStudio to generate my Schemas/create my Rules and then import
them
into Postgres to create my tables and views - is there anyway for me to
view
them in Postgres to verify that they got imported correctly? I can view
the
.sql files that I imported to verify the CREATE functions, etc., but
when I
search these files, the CREATE RULE commands are not in there...
Thanks much,
-Jeanna
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Richard Broersma
Jr
Sent: Wednesday, January 10, 2007 5:04 PM
To: Jeanna Geier; pgsql-general
Subject: Re: [GENERAL] Problems With VIEWS
2007-01-10 16:45:33 ERROR: cannot insert into a view
2007-01-10 16:45:33 HINT: You need an unconditional ON INSERT DO
INSTEAD
rule.
Also, I see you have this field in your insert rule:
new.upgrade_date
but I see so upgrade_date in you view's definition. What exactly do you
intend to be inserted.
All of the New.[field_names] in the insert statement must be view
fieldnames
not table fieldnames.
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 6: explain analyze is your friend
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Optimize expresiions.
[EMAIL PROTECTED] wrote: On Thursday 11 January 2007 12:34, Richard Huxton wrote: The query would run much faster with the filter reordered. Is there a way to tell the planner/optimizer that certain functions are more expensive than others, and should be postponed in lazy evaluation ? Or is there a hook in the system that would allow me too look at and maybe reorder expressions before they are executed ? Not really. Perhaps look into using a functional/expressional index and see if that does the job for you. Oh well, pity. Thanks anyway. In my case, indexes don't help. Maybe your functions can be marked STABLE or even IMMUTABLE? That should help the planner evaluate them less often. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Problems With VIEWS
So, herein probably lies my problem with the Cannont insert into a view error I'm getting anytime I'm attempting to access it from my program - there are no rules set up for them, right? Correct, without insert rules you will not be able to add new records to the underlying tables of a view. So, if I manually enter them for each VIEW and do a BACKUP and then RESTORE, should they still be attached to each of the VIEWS? What exactly would you be backing up and restoring? I expect that you can add new rules to these views from psql without even disconnecting from CASEStudio or anyother client program. Once the rulse are added, inserts that did not work before will start working. I would think CASEStudio would have this capabilty to export these, but at this point I need to get our application and the VIEWS working and if I need to take the time to enter each RULE for the VIEWS manually, so be it. Either way. I like to create sql files with all of the DDL for creating the view and rules. Overtime, if I need to change my view or reconfigure the rules, I can edit my sql file and then call it up in psql using \e view_def.sql I use the CREATE OR REPLACE VIEW syntax to achieve this. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Problems With VIEWS
On Thu, 11 Jan 2007 06:36:34 -0800 (PST), Richard Broersma Jr [EMAIL PROTECTED] wrote: Either way. I like to create sql files with all of the DDL for creating the view and rules. Overtime, if I need to change my view or reconfigure the rules, I can edit my sql file and then call it up in psql using \e view_def.sql I use the CREATE OR REPLACE VIEW syntax to achieve this. Additional note: REPLACE doesn't work if you are going to change the list/type/name of your view columns. In 8.2 and above you could use DROP VIEW IF EXISTS combined with a following CREATE VIEW, instead. Bernd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Problems With VIEWS
Bernd Helmle wrote: On Thu, 11 Jan 2007 06:36:34 -0800 (PST), Richard Broersma Jr [EMAIL PROTECTED] wrote: Either way. I like to create sql files with all of the DDL for creating the view and rules. Overtime, if I need to change my view or reconfigure the rules, I can edit my sql file and then call it up in psql using \e view_def.sql I use the CREATE OR REPLACE VIEW syntax to achieve this. Additional note: REPLACE doesn't work if you are going to change the list/type/name of your view columns. In 8.2 and above you could use DROP VIEW IF EXISTS combined with a following CREATE VIEW, instead. Does anybody have a reason why this is the case. I can change all those things for a table without dropping it, why can't I do the same on a view? Bernd ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Cluster all tables in database to PK index
Besides writing a script that looks through the DDL of all tables, and CLUSTERs all tables with PK constraints, is there a quicker way? Is this really a sensible thing to do? As often as not, you want to cluster on foreign keys... -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] crosstab - pivot - transpose
Hi, I will need to create a crosstab representation of my resultset. The resultset looks like this: rowid, columnid, cellvalue I don't know beforehand the number of columns and their id's but they will be close to 200. I have looked at the crosstab tablefunction but it seems that you have to know your data in advance to use that. I looked at the posts here and many said that it can (only/easily) be done in a language like plperl. Does somebody have an example for this simple case? Thanks for the help. Balázs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Checkpoint request failed on version 8.2.1.
Hi all. I'm getting a checkpoint request failed message when I try to execute a CREATE DATABASE command. Since it was a fresh install, I've included the entire server log up to the point of the error. I truncated the log output two lines after the error message. Is there a way I can avoid this problem? Is this a bug? Thanks. Patrick 2007-01-11 09:46:20 LOG: database system was shut down at 2007-01-11 09:46:16 Mountain Standard Time 2007-01-11 09:46:20 LOG: checkpoint record is at 0/487970 2007-01-11 09:46:20 LOG: redo record is at 0/487970; undo record is at 0/0; shutdown TRUE 2007-01-11 09:46:20 LOG: next transaction ID: 0/595; next OID: 10820 2007-01-11 09:46:20 LOG: next MultiXactId: 1; next MultiXactOffset: 0 2007-01-11 09:46:20 LOG: database system is ready 2007-01-11 09:47:51 LOG: received SIGHUP, reloading configuration files 2007-01-11 09:48:18 FATAL: no pg_hba.conf entry for host 192.168.2.102, user sa, database postgres, SSL off 2007-01-11 09:55:39 LOG: could not receive data from client: No connection could be made because the target machine actively refused it. 2007-01-11 09:55:39 LOG: unexpected EOF on client connection 2007-01-11 09:55:59 NOTICE: CREATE TABLE will create implicit sequence Transactions_Transaction_ID_seq for serial column Transactions.Transaction_ID 2007-01-11 09:55:59 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index Transactions_pkey for table Transactions 2007-01-11 09:56:05 NOTICE: CREATE TABLE will create implicit sequence Database_Updates_ID_seq for serial column Database_Updates.ID 2007-01-11 09:56:05 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index Database_Updates_pkey for table Database_Updates 2007-01-11 09:56:06 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index Key_pkey for table Key 2007-01-11 09:56:07 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index UnitTestTable1_pkey for table UnitTestTable1 2007-01-11 09:56:07 NOTICE: trigger Delete_Tracking_Trigger for table UnitTestTable1 does not exist, skipping 2007-01-11 09:56:07 NOTICE: trigger Update_Tracking_Trigger for table UnitTestTable1 does not exist, skipping 2007-01-11 09:56:07 NOTICE: trigger Insert_Tracking_Trigger for table UnitTestTable1 does not exist, skipping 2007-01-11 09:56:10 NOTICE: drop cascades to sequence _WEBDRIL.IDSequence 2007-01-11 09:56:10 NOTICE: drop cascades to table _WEBDRIL.Database_Id 2007-01-11 09:56:10 NOTICE: drop cascades to table _WEBDRIL.Database_Updates 2007-01-11 09:56:10 NOTICE: drop cascades to default for table _WEBDRIL.Database_Updates column ID 2007-01-11 09:56:10 NOTICE: drop cascades to table _WEBDRIL.Transactions 2007-01-11 09:56:10 NOTICE: drop cascades to default for table _WEBDRIL.Transactions column Transaction_ID 2007-01-11 09:56:10 NOTICE: drop cascades to function _WEBDRIL.Get_Transaction_ID() 2007-01-11 09:56:10 NOTICE: drop cascades to function _WEBDRIL.Current_Transaction_ID() 2007-01-11 09:56:10 NOTICE: drop cascades to function _WEBDRIL.Get_User_ID() 2007-01-11 09:56:11 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index UnitTestTable1_pkey for table UnitTestTable1 2007-01-11 09:56:11 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index UnitTestTable2_pkey for table UnitTestTable2 2007-01-11 09:56:17 ERROR: could not open relation 1663/16403/16426: Permission denied 2007-01-11 09:56:17 ERROR: checkpoint request failed 2007-01-11 09:56:17 HINT: Consult recent messages in the server log for details. 2007-01-11 09:56:17 STATEMENT: CREATE DATABASE TestDatabase 2007-01-11 09:56:18 NOTICE: drop cascades to table TestSchema.stable ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Remove duplicate rows
Hello, I need to remove duplicates rows from a subquery but order these results by a column what is not selected. There are logically two solutions but no works. SELECT DISTINCT sub.foo FROM (SELECT ...) AS sub ORDER BY sub.bar ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list SELECT sub.foo FROM (SELECT ...) AS sub GROUP BY sub.foo ORDER BY sub.bar ERROR: column sub.bar must appear in the GROUP BY clause or be used in an aggregate function Does anybody know how to remove duplicate rows from a subquery and order these results by a column what is not selected but exists in a subquery? Thanks for any advice, J.N. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Checkpoint request failed on version 8.2.1.
Patrick Earl [EMAIL PROTECTED] writes: 2007-01-11 09:56:17 ERROR: could not open relation 1663/16403/16426: Permission denied 2007-01-11 09:56:17 ERROR: checkpoint request failed 2007-01-11 09:56:17 HINT: Consult recent messages in the server log for details. 2007-01-11 09:56:17 STATEMENT: CREATE DATABASE TestDatabase Well, like the HINT says, consult recent messages for details. In this case the reason the checkpoint failed was evidently a file permissions problem. Is this repeatable? What platform is it on? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Remove duplicate rows
Jiří Němec wrote: Hello, I need to remove duplicates rows from a subquery but order these results by a column what is not selected. There are logically two solutions but no works. SELECT DISTINCT sub.foo FROM (SELECT ...) AS sub ORDER BY sub.bar ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list I'm not sure here, so I'll leave it alone. SELECT sub.foo FROM (SELECT ...) AS sub GROUP BY sub.foo ORDER BY sub.bar ERROR: column sub.bar must appear in the GROUP BY clause or be used in an aggregate function The problem here is that you are not really asking a meaningful question... foo bar 1 1 1 2 now, you are selecting foo, but you want to order by bar. What decision should be made about which value of bar to pick, so you can order on it? Regards Russell Smith Does anybody know how to remove duplicate rows from a subquery and order these results by a column what is not selected but exists in a subquery? Thanks for any advice, J.N. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] TRIGGER BEFORE INSERT
On Thu, 2007-01-11 at 15:10 +0100, Alban Hertroys wrote: Rafal Pietrak wrote: Hi! I'm re-posting this message again in hope someone would have a look at the case again. .. it's pending. You were given a solution; defer the foreign key constraint. Well. I were, but probably I'm doing something wrong with 'deferring the trigger'. When I put: SET CONSTRAINTS ALL DEFERRED ; *before* the UPDATE statement *within* the trigger function (just after BEGIN statement there). After doing so, notheing changes. I get the same ERROR. (all screenlogs were included in previous posts, so I'll spare those here). So may be SET CONSTRAINTS DEFERRED should be used somehow differently? I've never had any use for that construct, may be I miss something? Alternatively, you may want to re-think your trigger function so that it does things in the right order. I cannot see *any* way to reorder the events in the triger function. The function is short anough 'not to allow' :) for reordering - it just makes an UPDATE to some other table (where it puts a reference to the 'fresh ROW') and stores the result of that update in the newly created ROW. And the problem is, that UPDATE puts a reference to the fresh ROW and that the UPDATE statement does NOT SEE the 'freshly created ROW' - may be this is not a case of 'too early constraint check', but rather a problem of 'visibility' of data (new data) within a single transaction (an UPDATE is launched within the trigger transaction - should see already created ROW, shouldn't it?). N.B. All the code that does fail is in my previous posts. But as this is the 'second round' of my 'call for help' - I get an impression, that there may actually not be a solution. Too bad. -R ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] TRIGGER BEFORE INSERT
On Thu, Jan 11, 2007 at 07:26:32PM +0100, Rafal Pietrak wrote: Well. I were, but probably I'm doing something wrong with 'deferring the trigger'. When I put: SET CONSTRAINTS ALL DEFERRED ; *before* the UPDATE statement *within* the trigger function (just after BEGIN statement there). 1. Doing it within a function has no effect. 2. By default foreign key checks are not deferrable. Did you make yours deferrable? So may be SET CONSTRAINTS DEFERRED should be used somehow differently? I've never had any use for that construct, may be I miss something? Only at the beginning of a transaction and it only works on foreign keys marked deferrable. I cannot see *any* way to reorder the events in the triger function. The function is short anough 'not to allow' :) for reordering - it just makes an UPDATE to some other table (where it puts a reference to the 'fresh ROW') and stores the result of that update in the newly created ROW. A BEFORE trigger cannot see the row, nor can anything called by that trigger. If you want to call functions that want to see that row, use an AFTER trigger. And the problem is, that UPDATE puts a reference to the fresh ROW and that the UPDATE statement does NOT SEE the 'freshly created ROW' - may be this is not a case of 'too early constraint check', but rather a problem of 'visibility' of data (new data) within a single transaction (an UPDATE is launched within the trigger transaction - should see already created ROW, shouldn't it?). BEFORE trigger, no. AFTER trigger, yes. That's the difference between the two types... But as this is the 'second round' of my 'call for help' - I get an impression, that there may actually not be a solution. Too bad. It's possible, by making your foreign key INITIALLY DEFERRED. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] ORDER BY col is NULL in UNION causes error?
Tom Lane wrote: Michael Glaesemann [EMAIL PROTECTED] writes: On Dec 26, 2006, at 18:39 , Mike Benoit wrote: ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the result columns Even though state is a column in both tables, the order by is using an expression, rather than a column. ... I'm not sure of the underlying reasons why your query doesn't work, but give these a shot. There are some implementation reasons for not supporting expressions computed on a UNION result without an intervening sub-SELECT. It's too late at night for me to recall exactly what they are :-( --- one is that an Append plan node doesn't do any expression evaluation, but I think there are some more-subtle issues too. Suffice it to say that we could support this if we wanted to throw enough effort at it, but so far other problems have seemed more pressing. In the meantime, it seems like the above-quoted error message is not clear enough, since Mike failed to get the point that the ORDER BY item has to be just a simple column name of the UNION output. Anyone have a suggestion for better wording? I have updated the wording from ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the result columns))); to: ORDER BY on a UNION/INTERSECT/EXCEPT result must match existing result columns))); The 'match' wording might help, rather then 'use'. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Checkpoint request failed on version 8.2.1.
We're getting the error as part of an automated test suite and it is seems to occur every time the suite is run. The platform is Win XP 64 bit. When running the same unit test suite from a remote machine, the error does not occur. The error also does not occur when manually running the create database command using pgadmin. We are logging into PostgreSQL as the same user. Patrick On 1/11/07, Tom Lane [EMAIL PROTECTED] wrote: Patrick Earl [EMAIL PROTECTED] writes: 2007-01-11 09:56:17 ERROR: could not open relation 1663/16403/16426: Permission denied 2007-01-11 09:56:17 ERROR: checkpoint request failed 2007-01-11 09:56:17 HINT: Consult recent messages in the server log for details. 2007-01-11 09:56:17 STATEMENT: CREATE DATABASE TestDatabase Well, like the HINT says, consult recent messages for details. In this case the reason the checkpoint failed was evidently a file permissions problem. Is this repeatable? What platform is it on? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] ORDER BY col is NULL in UNION causes error?
On Thu, 2007-01-11 at 13:44 -0500, Bruce Momjian wrote: Tom Lane wrote: Michael Glaesemann [EMAIL PROTECTED] writes: On Dec 26, 2006, at 18:39 , Mike Benoit wrote: ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the result columns Even though state is a column in both tables, the order by is using an expression, rather than a column. ... I'm not sure of the underlying reasons why your query doesn't work, but give these a shot. There are some implementation reasons for not supporting expressions computed on a UNION result without an intervening sub-SELECT. It's too late at night for me to recall exactly what they are :-( --- one is that an Append plan node doesn't do any expression evaluation, but I think there are some more-subtle issues too. Suffice it to say that we could support this if we wanted to throw enough effort at it, but so far other problems have seemed more pressing. In the meantime, it seems like the above-quoted error message is not clear enough, since Mike failed to get the point that the ORDER BY item has to be just a simple column name of the UNION output. Anyone have a suggestion for better wording? I have updated the wording from ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the result columns))); to: ORDER BY on a UNION/INTERSECT/EXCEPT result must match existing result columns))); The 'match' wording might help, rather then 'use'. That helps some, but I'm sure it could be even more clear. The main issue is that you can't order by an expression computed by unions, correct? So couldn't the error message by something like: ORDER BY on a UNION/INTERSECT/EXCEPT result must match existing result columns and not be an expression))); -- Mike Benoit [EMAIL PROTECTED] signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Trying to load MySQL data
On 1/10/07, Dimitri Fontaine [EMAIL PROTECTED] wrote: Hi, Le mercredi 10 janvier 2007 02:54, [EMAIL PROTECTED] a écrit: I am working on a project where we are converting from MySQL to Postgres. I figured the easiest way would be to export the MySQL data as CSV. If you are using pg 8.2+, I've had good luck with the following: 1. create pgsql schema by hand or using some method, so they match mysql 2. mysqldump --compatible=postgresql [yadda] | grep ^INSERT | psql [yadda] this will work for most data types. as of 8.2, postgresql supports multiple record inserts, which while not as fast as copy, is pretty close. if mysqldump is dumping single line inserts, change it to multiple with -e switch iiirc. merlin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] generate_series with month intervals
Hi list,
I'd like to generate the latest year dynamically with generate_series.
This select works day wise:
select date_trunc ('month', now())::date + s.a
from generate_series(0, 11) as s(a)
I tried this but it didn't work:
select date_trunc ('month', now())::date + interval s.a || ' months'
from generate_series(0, 11) as s(a)
This works but looks grotesque:
select distinct date_trunc ('month', now()::date + s.a)::date
from generate_series(0, 365) as s(a)
Is there a way to do this more elegantly?
Best regards,
Marcus
---(end of broadcast)---
TIP 6: explain analyze is your friend
[GENERAL] pg_standby testing notes
I am looking into using pg_standby (v3) in a warm standby system. I'm going to double check it, but same machine replication seemed to work ok. When I tried to do remote server log shipping however, I had some issues. Initial setup and launch is working ok, my archive command is: 'test ! -f /var/lib/pgsql/pitr/%f cp %p /var/lib/pgsql/pitr/%f' my restore command is: pg_standby -m -t/raid/pitr/kill /raid/pitr %f %p '/raid/pitr' is an nfs mount mounted from the primary to the standby. I suspect the problem lies there, but I thought I'd ask. here is my log on the standby following a fresh pitr load: 2007-01-11 07:40:37 EST : LOG: automatic recovery in progress 2007-01-11 07:40:37 EST : LOG: redo starts at 0/63B0 2007-01-11 07:41:37 EST : LOG: restored log file 00010064 from archive 2007-01-11 07:41:38 EST : LOG: restored log file 00010065 from archive 2007-01-11 07:41:38 EST : LOG: restored log file 00010066 from archive 2007-01-11 07:51:44 EST : LOG: could not open file pg_xlog/00010067 (log file 0, segment 103): No such file or directory 2007-01-11 07:51:44 EST : LOG: redo done at 0/66C8 2007-01-11 08:01:49 EST : PANIC: could not open file pg_xlog/00010066 (log file 0, segment 102): No such file or directory neither the primary or the standby are in production...the 3 restored files were from me manufacturing work on the primary to generate files. Is there anything obvious I should be checking? merlin ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] ORDER BY col is NULL in UNION causes error?
Mike Benoit wrote: That helps some, but I'm sure it could be even more clear. The main issue is that you can't order by an expression computed by unions, correct? So couldn't the error message by something like: ORDER BY on a UNION/INTERSECT/EXCEPT result must match existing result columns and not be an expression))); Well, it can be an expression _if_ the expression _matches_ an existing UNION column. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Checkpoint request failed on version 8.2.1.
Patrick Earl [EMAIL PROTECTED] writes: We're getting the error as part of an automated test suite and it is seems to occur every time the suite is run. The platform is Win XP 64 bit. Hm. We've seen problems of this ilk caused by bogus antivirus software, but if that were the explanation it's not clear why running the test locally vs remotely would change anything. Another theory is that it's got something to do with Windows' problems with unlinking open files --- we've found that permission denied can be the error code when attempting to open a file that's been unlinked but isn't yet gone because someone is holding it open. Can you determine whether the complained-of relation is one that's recently been deleted? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Trying to load MySQL data
Merlin Moncure wrote: On 1/10/07, Dimitri Fontaine [EMAIL PROTECTED] wrote: Hi, Le mercredi 10 janvier 2007 02:54, [EMAIL PROTECTED] a ?crit: I am working on a project where we are converting from MySQL to Postgres. I figured the easiest way would be to export the MySQL data as CSV. If you are using pg 8.2+, I've had good luck with the following: 1. create pgsql schema by hand or using some method, so they match mysql 2. mysqldump --compatible=postgresql [yadda] | grep ^INSERT | psql [yadda] Wow, mysqldump has a postgresql compatibility mode? Intersting. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Trying to load MySQL data
On 1/11/07, Bruce Momjian [EMAIL PROTECTED] wrote: Merlin Moncure wrote: On 1/10/07, Dimitri Fontaine [EMAIL PROTECTED] wrote: Hi, Le mercredi 10 janvier 2007 02:54, [EMAIL PROTECTED] a ?crit: I am working on a project where we are converting from MySQL to Postgres. I figured the easiest way would be to export the MySQL data as CSV. If you are using pg 8.2+, I've had good luck with the following: 1. create pgsql schema by hand or using some method, so they match mysql 2. mysqldump --compatible=postgresql [yadda] | grep ^INSERT | psql [yadda] Wow, mysqldump has a postgresql compatibility mode? Intersting. It does (had it for years), but it doesn't do very much...fixes the quotes and a couple of other things. In particular I know of no easy ways to convert the table schemas without use of external tools. merlin ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] ORDER BY col is NULL in UNION causes error?
Bruce Momjian [EMAIL PROTECTED] writes: Well, it can be an expression _if_ the expression _matches_ an existing UNION column. You're mistaken. It has to be *an output column name*. Not anything else. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] TRIGGER BEFORE INSERT
Martijn van Oosterhout kleptog@svana.org writes: On Thu, Jan 11, 2007 at 07:26:32PM +0100, Rafal Pietrak wrote: So may be SET CONSTRAINTS DEFERRED should be used somehow differently? I've never had any use for that construct, may be I miss something? Only at the beginning of a transaction and it only works on foreign keys marked deferrable. No, you can do it mid-transaction. I think the problem is the second part: the FK constraint has to be marked deferrable, which is not the default I believe. Also, you could forget the SET CONSTRAINTS entirely if you made the constraint INITIALLY DEFERRED ... then it'd always act as a deferred check. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] ERROR: invalid memory alloc request size, and others
Jonathan Hedstrom wrote: We downloaded the most recent stock FC6 kernel and rebooted to that. Hopefully this will take care of the issue. We've been up and running for 2 days now on the stock kernel, and haven't seen any of these errors. I'm thinking the issue is resolved. Thanks again for all the replies. -Jonathan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] generate_series with month intervals
Marcus Engene [EMAIL PROTECTED] writes:
I tried this but it didn't work:
select date_trunc ('month', now())::date + interval s.a || ' months'
from generate_series(0, 11) as s(a)
People keep trying that :-(. The typename 'foo' syntax is for a
*literal constant* only. Instead use multiplication, something like
select (date_trunc('month', now())::date + s.a * '1 month'::interval)::date
from generate_series(0, 11) as s(a)
You'll want the cast back to date as the last step here because
date+interval will give timestamp.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Checkpoint request failed on version 8.2.1.
There is no antivirus software running on the machine. I'm not entirely sure how to determine which relation it is complaining about. I see a folder that corresponds to the middle number in the log, and I see numbers in the same range as the right number from the log. In any case, the unit tests remove all contents and schema within the database before starting, and they remove the tables they create as they proceed. Certainly there are many things have been recently deleted. Patrick On 1/11/07, Tom Lane [EMAIL PROTECTED] wrote: Patrick Earl [EMAIL PROTECTED] writes: We're getting the error as part of an automated test suite and it is seems to occur every time the suite is run. The platform is Win XP 64 bit. Hm. We've seen problems of this ilk caused by bogus antivirus software, but if that were the explanation it's not clear why running the test locally vs remotely would change anything. Another theory is that it's got something to do with Windows' problems with unlinking open files --- we've found that permission denied can be the error code when attempting to open a file that's been unlinked but isn't yet gone because someone is holding it open. Can you determine whether the complained-of relation is one that's recently been deleted? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] ORDER BY col is NULL in UNION causes error?
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Well, it can be an expression _if_ the expression _matches_ an existing UNION column. You're mistaken. It has to be *an output column name*. Not anything else. Yea, I was thinking of this: SELECT *, state IS NULL AS state_is_null FROM income_tax_rate_us UNION SELECT *, state IS NULL AS state_is_null FROM income_tax_rate_us ORDER BY state_is_null Here the column result is an expression, and you reference that. Updated wording: test= select * from test union select * from test order by x is null; ERROR: A UNION/INTERSECT/EXCEPT ORDER BY cannot use expressions or functions, only result column names -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Remove duplicate rows
On Thu, Jan 11, 2007 at 18:51:57 +0100, Jiří Němec [EMAIL PROTECTED] wrote: Hello, I need to remove duplicates rows from a subquery but order these results by a column what is not selected. There are logically two solutions but no works. SELECT DISTINCT sub.foo FROM (SELECT ...) AS sub ORDER BY sub.bar ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list SELECT sub.foo FROM (SELECT ...) AS sub GROUP BY sub.foo ORDER BY sub.bar ERROR: column sub.bar must appear in the GROUP BY clause or be used in an aggregate function Does anybody know how to remove duplicate rows from a subquery and order these results by a column what is not selected but exists in a subquery? Is that column dependent (just on) the column you are checking for duplicates on? If so you can use GROUP BY on both columns, listing the column you want to order by first. If not, you might want to take a look at DISTINCT ON. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] ORDER BY col is NULL in UNION causes error?
Bruce Momjian [EMAIL PROTECTED] writes: Updated wording: test= select * from test union select * from test order by x is null; ERROR: A UNION/INTERSECT/EXCEPT ORDER BY cannot use expressions or functions, only result column names This does not meet the style guidelines. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] generate_series with month intervals
Tom Lane skrev:
Marcus Engene [EMAIL PROTECTED] writes:
I tried this but it didn't work:
select date_trunc ('month', now())::date + interval s.a || ' months'
from generate_series(0, 11) as s(a)
People keep trying that :-(. The typename 'foo' syntax is for a
*literal constant* only. Instead use multiplication, something like
select (date_trunc('month', now())::date + s.a * '1 month'::interval)::date
from generate_series(0, 11) as s(a)
You'll want the cast back to date as the last step here because
date+interval will give timestamp.
regards, tom lane
Wonderful! Worked like a charm. Thanks!
Marcus
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Problems With VIEWS
Thanks, everyone - I really appreciate everyone's inupt and responses! Got this resolved and added the RULES to my .sql file. So far, so good. -Jeanna -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Bernd Helmle Sent: Thursday, January 11, 2007 9:02 AM To: Richard Broersma Jr Cc: Jeanna Geier; Adam Rich; pgsql-general Subject: Re: [GENERAL] Problems With VIEWS On Thu, 11 Jan 2007 06:36:34 -0800 (PST), Richard Broersma Jr [EMAIL PROTECTED] wrote: Either way. I like to create sql files with all of the DDL for creating the view and rules. Overtime, if I need to change my view or reconfigure the rules, I can edit my sql file and then call it up in psql using \e view_def.sql I use the CREATE OR REPLACE VIEW syntax to achieve this. Additional note: REPLACE doesn't work if you are going to change the list/type/name of your view columns. In 8.2 and above you could use DROP VIEW IF EXISTS combined with a following CREATE VIEW, instead. Bernd ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] ORDER BY col is NULL in UNION causes error?
Bruce Momjian wrote: Here the column result is an expression, and you reference that. Updated wording: test= select * from test union select * from test order by x is null; ERROR: A UNION/INTERSECT/EXCEPT ORDER BY cannot use expressions or functions, only result column names This seems at odds with our message style guidelines. I'd suggest putting the actual error message in errmsg() and the rest as a errhint, maybe (errmsg(invalid UNION/INTERSECT/EXCEPT ORDER BY clause), errhint(You may specify only output result column names in the ORDER BY clauses.)) or something like that. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] TRIGGER BEFORE INSERT
On Thursday 11 January 2007 10:26 am, Rafal Pietrak wrote: On Thu, 2007-01-11 at 15:10 +0100, Alban Hertroys wrote: Rafal Pietrak wrote: Hi! I'm re-posting this message again in hope someone would have a look at the case again. .. it's pending. You were given a solution; defer the foreign key constraint. Well. I were, but probably I'm doing something wrong with 'deferring the trigger'. When I put: What previous posters have said is that you need to defer the FK. So you need to change your table definition from: CREATE TABLE test_utarg(tm timestamp not null, nic int, amount int not null, dnia int references test_days(id)); to: CREATE TABLE test_utarg(tm timestamp not null, nic int, amount int not null, dnia int references test_days(id) INITIALLY DEFERRED); per Toms suggestion. This eliminates the need for the SET CONSTRAINTS DEFERRED statement. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] generate_series with month intervals
On Thu, Jan 11, 2007 at 20:07:29 +0100,
Marcus Engene [EMAIL PROTECTED] wrote:
Hi list,
I'd like to generate the latest year dynamically with generate_series.
This select works day wise:
This works but looks grotesque:
select distinct date_trunc ('month', now()::date + s.a)::date
from generate_series(0, 365) as s(a)
Is there a way to do this more elegantly?
Are you just trying to get a list off the first of the month for the current
month and the next 11 months after that? For that you want to get the first
of the current month and then add s.a * '1 month' to it for 0 to 11.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] ORDER BY col is NULL in UNION causes error?
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Updated wording: test= select * from test union select * from test order by x is null; ERROR: A UNION/INTERSECT/EXCEPT ORDER BY cannot use expressions or functions, only result column names This does not meet the style guidelines. Where are the style guidelines? I looked before but can't find them. I agree the second part could be a hint. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] RESTORE Error
Hi List! I'm attempting to do a restore of a database and I'm getting the following error the I'm not familiar with: COPY workorder (work_order_id, projectname, request_date, requestor, request_detail, request_completion_date, request_a... pg_restore: restoring data for table worksite pg_restore: [archiver (db)] Error from TOC entry 2584; 0 6536819 TABLE DATA worksite postgres pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at or near 255454 at character 1 Command was: 255454 3 WestSalem 2006-05-12 14:10:18.578-05 gregclean it 2006-05-12 17:00:00-05 W6037 Ruth Lane OnalaskaWisconsin 54650 ... pg_restore: restoring data for table BLOBS pg_restore: restored 0 large objects WARNING: errors ignored on restore: 73 Process returned exit code 1. The '255454' here is in the OID column of the table... And here's the CREATE stmt for the workorder table: CREATE TABLE workorder.workorder ( work_order_id serial NOT NULL, projectname varchar NOT NULL, request_date timestamptz, requestor varchar NOT NULL, request_detail varchar NOT NULL, request_completion_date timestamptz, request_address varchar, request_city varchar, request_state varchar, request_zip varchar, request_location_description varchar, request_floor varchar, request_department varchar, request_priority varchar, request_health_security_concern varchar, request_vandalism varchar, request_recurring_interval varchar, request_roomid varchar, request_tagid varchar, request_presence_required bool, request_available_date timestamptz, site_name varchar NOT NULL, authorizer varchar, date_authorized timestamptz, elementid int8, element_description varchar, priority varchar, work_detail varchar, est_start_time timestamptz, actual_start_time timestamptz, est_completion_time timestamptz, actual_completion_time timestamptz, actual_labor_cost numeric, actual_material_cost numeric, comments varchar, recurrence_interval int8, account_name varchar, CONSTRAINT pk_workorder PRIMARY KEY (work_order_id), CONSTRAINT Relationship238 FOREIGN KEY (projectname) REFERENCES project (projectname) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT Relationship245 FOREIGN KEY (requestor) REFERENCES projectuser (userid) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT Relationship246 FOREIGN KEY (authorizer) REFERENCES projectuser (userid) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT Relationship254 FOREIGN KEY (projectname, account_name) REFERENCES projectdata.account (projectname, account_name) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT Relationship265 FOREIGN KEY (projectname, site_name) REFERENCES workorder.worksite (projectname, site_name) ON UPDATE CASCADE ON DELETE RESTRICT ) Any thoughts or ideas on the error? Thanks in advance for your help! -Jeanna ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] TRIGGER BEFORE INSERT
Perfect! Thenx! -R On Thu, 2007-01-11 at 12:26 -0800, Adrian Klaver wrote: On Thursday 11 January 2007 10:26 am, Rafal Pietrak wrote: On Thu, 2007-01-11 at 15:10 +0100, Alban Hertroys wrote: Rafal Pietrak wrote: Hi! I'm re-posting this message again in hope someone would have a look at the case again. .. it's pending. You were given a solution; defer the foreign key constraint. Well. I were, but probably I'm doing something wrong with 'deferring the trigger'. When I put: What previous posters have said is that you need to defer the FK. So you need to change your table definition from: CREATE TABLE test_utarg(tm timestamp not null, nic int, amount int not null, dnia int references test_days(id)); to: CREATE TABLE test_utarg(tm timestamp not null, nic int, amount int not null, dnia int references test_days(id) INITIALLY DEFERRED); per Toms suggestion. This eliminates the need for the SET CONSTRAINTS DEFERRED statement. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] RESTORE Error
Just a bit more info: Running Postgres 8.0 and the: 255454 3 WestSalem 2006-05-12 14:10:18.578-05 greg clean it 2006-05-12 17:00:00-05 W6037 Ruth Lane OnalaskaWisconsin 54650 ... is a row (the first row) that should be inserted/restored into the 'workorder.workorder' table. Hope that helps! -Jeanna -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Jeanna Geier Sent: Thursday, January 11, 2007 3:10 PM To: pgsql-general Subject: [GENERAL] RESTORE Error Hi List! I'm attempting to do a restore of a database and I'm getting the following error the I'm not familiar with: COPY workorder (work_order_id, projectname, request_date, requestor, request_detail, request_completion_date, request_a... pg_restore: restoring data for table worksite pg_restore: [archiver (db)] Error from TOC entry 2584; 0 6536819 TABLE DATA worksite postgres pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at or near 255454 at character 1 Command was: 255454 3 WestSalem 2006-05-12 14:10:18.578-05 gregclean it 2006-05-12 17:00:00-05 W6037 Ruth Lane OnalaskaWisconsin 54650 ... pg_restore: restoring data for table BLOBS pg_restore: restored 0 large objects WARNING: errors ignored on restore: 73 Process returned exit code 1. The '255454' here is in the OID column of the table... And here's the CREATE stmt for the workorder table: CREATE TABLE workorder.workorder ( work_order_id serial NOT NULL, projectname varchar NOT NULL, request_date timestamptz, requestor varchar NOT NULL, request_detail varchar NOT NULL, request_completion_date timestamptz, request_address varchar, request_city varchar, request_state varchar, request_zip varchar, request_location_description varchar, request_floor varchar, request_department varchar, request_priority varchar, request_health_security_concern varchar, request_vandalism varchar, request_recurring_interval varchar, request_roomid varchar, request_tagid varchar, request_presence_required bool, request_available_date timestamptz, site_name varchar NOT NULL, authorizer varchar, date_authorized timestamptz, elementid int8, element_description varchar, priority varchar, work_detail varchar, est_start_time timestamptz, actual_start_time timestamptz, est_completion_time timestamptz, actual_completion_time timestamptz, actual_labor_cost numeric, actual_material_cost numeric, comments varchar, recurrence_interval int8, account_name varchar, CONSTRAINT pk_workorder PRIMARY KEY (work_order_id), CONSTRAINT Relationship238 FOREIGN KEY (projectname) REFERENCES project (projectname) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT Relationship245 FOREIGN KEY (requestor) REFERENCES projectuser (userid) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT Relationship246 FOREIGN KEY (authorizer) REFERENCES projectuser (userid) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT Relationship254 FOREIGN KEY (projectname, account_name) REFERENCES projectdata.account (projectname, account_name) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT Relationship265 FOREIGN KEY (projectname, site_name) REFERENCES workorder.worksite (projectname, site_name) ON UPDATE CASCADE ON DELETE RESTRICT ) Any thoughts or ideas on the error? Thanks in advance for your help! -Jeanna ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] documentation vs reality: template databases
running 8.1 on a fedora core 5 linux box, up to date so far as i know. this page: http://www.postgresql.org/docs/8.1/interactive/manage-ag-templatedbs.html says the following: Note: template1 and template0 do not have any special status beyond the fact that the name template1 is the default source database name for CREATE DATABASE. For example, one could drop template1 and recreate it from template0 without any ill effects. This course of action might be advisable if one has carelessly added a bunch of junk in template1. having just carelessly loaded a backup into template1 instead of the place i wanted to load it, i had a chance to try to follow this advice: foo=# drop database template1; ERROR: cannnot drop a template database foo=# cheers, richard ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] ORDER BY col is NULL in UNION causes error?
Bruce Momjian [EMAIL PROTECTED] writes: Where are the style guidelines? I looked before but can't find them. http://developer.postgresql.org/pgdocs/postgres/error-style-guide.html regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] RESTORE Error
And log file shows the following (what I posted before was through pgAdmin): 2007-01-11 15:38:17 LOG: could not load root certificate file C:/Program Files/PostgreSQL/8.0/data/root.crt: No such file or directory 2007-01-11 15:38:17 DETAIL: Will not verify client certificates. 2007-01-11 15:38:47 ERROR: relation temp_measurement does not exist 2007-01-11 15:38:47 ERROR: syntax error at or near 7094982 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 67459 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 67464 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 67465 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 252460 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 67478 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 379654 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 379580 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 255624 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 67484 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 3378657 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 115706 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 3370733 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 67494 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 379576 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 67530 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 718343 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 3362828 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 67783 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 67785 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 3410163 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 67788 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 67808 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 67814 at character 1 2007-01-11 15:38:48 ERROR: syntax error at or near 67830 at character 1 2007-01-11 15:38:48 ERROR: syntax error at or near 113205 at character 1 2007-01-11 15:38:48 ERROR: syntax error at or near 113574 at character 1 2007-01-11 15:38:48 ERROR: syntax error at or near 113579 at character 1 2007-01-11 15:38:48 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:48 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:48 ERROR:
Re: [GENERAL] remove embedded carriage returns
[EMAIL PROTECTED] wrote: Outputting a SELECT statement's results to ascii file showed me a table with a bunch of embedded carriage return characters in the values. I want to remove the embedded returns, so I read the documentation and tried a few variations on SELECT replace(columname, 'chr(13)','') from tablename with no luck. Could someone help me rid this table of its embedded returns? Thanks in advance... Is there a different board that I should post this question to? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Question - Query based on WHERE OR
I'm a rank newbie to Postgres am having a hard time getting my arms around this. I'm trying to construct a query to be run in a PHP script. I have an HTML form were someone can enter either a last name or a social security number then query the database based on what they entered. My query looks like this: SELECT foo, baz, bar FROM public.table WHERE lastname ~* '$lastname' OR ssn='$ssn' I need to leave the last name a wildcard in case someone enters a partial name, lower case / upper case, etc. I want the SSN to match exactly if they search by that. The way it's written, if I enter nothing for the last name, it returns all rows, regardless of what I enter for the ssn. I understand why it happens, but what can I do to return the desired results? Thank you. Mike ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] remove embedded carriage returns
Jonathan Hedstrom wrote: [EMAIL PROTECTED] wrote: SELECT replace(columname, 'chr(13)','') from tablename Try using chr(13) without the single quotes: SELECT replace(columname, chr(13),'') from tablename or you could use '\r' to get the character: SELECT replace(columname, E'\r','') from tablename -Jonathan begin:vcard fn:Jonathan Hedstrom n:Hedstrom;Jonathan org:Downtown Emergency Service Center;Information Services email;internet:[EMAIL PROTECTED] title:Data Systems Administrator tel;work:(206) 464-1570 ext. 3014 version:2.1 end:vcard ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster Thanks so much! I am a psql noob and really appreciate your response and help. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] storing SMALL large objects to postgres with C# (.NET ODBC layer)
Hello,
I've got a problem inserting binary objects into the postgres database.
I have binary objects (e.g. images or smth else) of any size which I
want to insert into the database. Funny is it works for files larger
than 8000 Bytes. If a file is less than 1000 Bytes I get the following
message:
Error message:
-- invalid input syntax for type oid: \074\077..;
If a file is between 1000 and 8000 Bytes my program just crashes
silently. This is a really strange behaviour since the binary files
vary in sizes (big and small onces). It works with C++ using the
SQLPutData class provided by the ODBC layer. In C# I don't have
anything like that (right?).
So, now the question is: Is that an error in my program, my way of
accessing the database, of creating the insert command, of the C# .NET
code, or the postgreSQL ODBC driver I use? I doubt it is the last one,
since it works with C++. Can anyone help me please??
Technical output (source code, logs, etc):
--
I use:
- WinXP SP2
- VS 2005
- Database version: 8.2.0
- PostgreSQL ANSI Driver Version 8.02.02.00
- I create a ODBC DSN entry
- standard settings
- bytea as LO unchecked
- unchecked LF conversion
Here is my C# code to write the binary object:
public int InsertBLOB(byte[] blob) {
OdbcDataAdapter adapter = null;
try {
adapter = new OdbcDataAdapter();
adapter.InsertCommand = new OdbcCommand();
adapter.InsertCommand.CommandText = INSERT INTO
binary_object (object) VALUES (?);
adapter.InsertCommand.Connection = new
OdbcConnection(this._connection.ConnectionString); // my DSN
connection string
// here the binary parameter is defined
adapter.InsertCommand.Parameters.Add(blob,
OdbcType.VarBinary);
adapter.InsertCommand.Parameters[blob].Direction =
ParameterDirection.Input;
adapter.InsertCommand.Parameters[blob].Value = blob;
} catch (Exception e) {
LogError(e);
}
try {
adapter.InsertCommand.Connection.Open();
int rowsAffected =
adapter.InsertCommand.ExecuteNonQuery();
adapter.InsertCommand.Connection.Close();
} catch (Exception e) {
LogError(e);
}
}
Here is my database log from the insert statement from an insert when
the file is larger than 8000 Bytes:
-- obviously the LO with the oid 121156 was created successfully
2007-01-11 17:44:35 LOCATION: exec_simple_query, postgres.c:811
2007-01-11 17:44:41 LOG: 0: statement: set DateStyle to 'ISO'
2007-01-11 17:44:41 LOCATION: exec_simple_query, postgres.c:811
2007-01-11 17:44:41 LOG: 0: statement: set geqo to 'OFF'
2007-01-11 17:44:41 LOCATION: exec_simple_query, postgres.c:811
2007-01-11 17:44:41 LOG: 0: statement: set extra_float_digits to 2
2007-01-11 17:44:41 LOCATION: exec_simple_query, postgres.c:811
2007-01-11 17:44:41 LOG: 0: statement: select oid, typbasetype
from pg_type where typname = 'lo'
2007-01-11 17:44:41 LOCATION: exec_simple_query, postgres.c:811
2007-01-11 17:44:41 LOG: 0: statement: select pg_client_encoding()
2007-01-11 17:44:41 LOCATION: exec_simple_query, postgres.c:811
2007-01-11 17:44:41 LOG: 0: statement: BEGIN
2007-01-11 17:44:41 LOCATION: exec_simple_query, postgres.c:811
2007-01-11 17:44:41 LOG: 0: fastpath function call: lo_creat
(OID 957)
2007-01-11 17:44:41 LOCATION: HandleFunctionRequest, fastpath.c:333
2007-01-11 17:44:41 LOG: 0: fastpath function call: lo_open (OID
952)
2007-01-11 17:44:41 LOCATION: HandleFunctionRequest, fastpath.c:333
2007-01-11 17:44:41 LOG: 0: fastpath function call: lo_lseek
(OID 956)
2007-01-11 17:44:41 LOCATION: HandleFunctionRequest, fastpath.c:333
2007-01-11 17:44:41 LOG: 0: fastpath function call: lowrite (OID
955)
2007-01-11 17:44:41 LOCATION: HandleFunctionRequest, fastpath.c:333
2007-01-11 17:44:41 LOG: 0: fastpath function call: lo_close
(OID 953)
2007-01-11 17:44:41 LOCATION: HandleFunctionRequest, fastpath.c:333
2007-01-11 17:44:41 LOG: 0: statement: COMMIT
2007-01-11 17:44:41 LOCATION: exec_simple_query, postgres.c:811
2007-01-11 17:44:41 LOG: 0: statement: INSERT INTO binary_object
(object) VALUES ('121156'::lo)
2007-01-11 17:44:41 LOCATION: exec_simple_query, postgres.c:811
2007-01-11 17:44:48 LOG: 0: statement: set DateStyle to 'ISO'
2007-01-11 17:44:48 LOCATION: exec_simple_query, postgres.c:811
2007-01-11 17:44:48 LOG: 0: statement: set geqo to 'OFF'
2007-01-11 17:44:48 LOCATION: exec_simple_query, postgres.c:811
2007-01-11 17:44:48 LOG: 0: statement: set extra_float_digits to 2
2007-01-11 17:44:48 LOCATION: exec_simple_query, postgres.c:811
2007-01-11 17:44:48 LOG: 0: statement: select oid, typbasetype
from pg_type where typname = 'lo'
2007-01-11 17:44:48 LOCATION: exec_simple_query,
Re: [GENERAL] Trying to load MySQL data
The missing quote after Poster is a mistake I made when sanitzing the data for posting here. That error is NOT present in the actual data. There is a quote where needed in the data. So, with that in mind, why am I still getting the error? Also, there is no symbol we can expect to not be in the data. This data is from several sources, including a message board, and there could be tildes, pipes, or any other symbol in discussion fields. Also since it's CSV we just have null represented by lack of any value between two commas, so: a,b,,d represents a row with values a, b, NULL, d. This works fine in general. Walter Vaughan wrote: [EMAIL PROTECTED] wrote: Hello, I am working on a project where we are converting from MySQL to Postgres. I figured the easiest way would be to export the MySQL data as CSV. I dunno, but unless you don't really care about your data, I'd use something that you have no chance of in your data. Things like | and ~ are pretty standard field separators. So you'd use things like FIELDS TERMINATED BY '|' in your MySQL export and WITH DELIMITER '|' NULL '' in your postgresql import Also remember that mySQL nulls are nothing like postgresql nulls. -- Walter ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] RESTORE Error
OK, so if there are several of these same types of errors (which I verified by running the RESTORE from the command line vs. pgAdmin - some with \ and some with the OID; how come it exits out on that one particulat one?? Any thoughts or ideas on that one?: COPY profession_type (projectname, profession_type) WITH OIDS FROM stdin; pg_restore: restoring data for table public_authorities pg_restore: [archiver (db)] Error from TOC entry 2545; 0 6536592 TABLE DATA publ ic_authorities postgres pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at or near \ at character 1 Command was: \. COPY subflooring (projectname, subfloor_id, covered_flooring, cover_floor_adhesi on, ... pg_restore: restoring data for table training_class pg_restore: [archiver (db)] Error from TOC entry 2550; 0 6536617 TABLE DATA trai ning_class postgres pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at or near 67783 at character 1 Command was: 67783 WestSalem CONC001 Concrete\N \N \N \N \N \N 67784 WestSalem \N \N \N \N \N \N \N \. COPY training_class (projectna... pg_restore: restoring data for table training_types pg_restore: [archiver (db)] Error from TOC entry 2551; 0 6536622 TABLE DATA trai ning_types postgres pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at or near \ at character 1 Command was: \. COPY arcelement (elementid, start, end, radius_x, radius_y, large_arc, sweep , rotation) WITH OIDS FROM stdin; pg_restore: restoring data for table attribute pg_restore: [archiver (db)] Error from TOC entry 2488; 0 6536241 TABLE DATA attr ibute postgres pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at or near 3410163 at character 1 Command was: 34101631810987 (2491.8225222024057,8759.2336777975943) (2440.9108777975944,8759.2336777975943) 35.69 35.69 f f 0 34... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Jeanna Geier Sent: Thursday, January 11, 2007 3:43 PM To: pgsql-general Subject: Re: [GENERAL] RESTORE Error And log file shows the following (what I posted before was through pgAdmin): 2007-01-11 15:38:17 LOG: could not load root certificate file C:/Program Files/PostgreSQL/8.0/data/root.crt: No such file or directory 2007-01-11 15:38:17 DETAIL: Will not verify client certificates. 2007-01-11 15:38:47 ERROR: relation temp_measurement does not exist 2007-01-11 15:38:47 ERROR: syntax error at or near 7094982 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 67459 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 67464 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 67465 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 252460 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 67478 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 379654 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 379580 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 255624 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 67484 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 3378657 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 115706 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 3370733 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 67494 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 379576 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near \ at character 1 2007-01-11 15:38:47 ERROR: syntax error at
Re: [GENERAL] Trying to load MySQL data
On Wed, 2007-01-10 at 16:51, [EMAIL PROTECTED] wrote: The missing quote after Poster is a mistake I made when sanitzing the data for posting here. That error is NOT present in the actual data. There is a quote where needed in the data. So, with that in mind, why am I still getting the error? Also, there is no symbol we can expect to not be in the data. This data is from several sources, including a message board, and there could be tildes, pipes, or any other symbol in discussion fields. Also since it's CSV we just have null represented by lack of any value between two commas, so: a,b,,d represents a row with values a, b, NULL, d. This works fine in general. Can you make a sanitized test case, complete unto itself, and post that? The data to look for are generally \ and ' ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Function which returns record
I am looking to have the select list passed into a function at runtime and
use this select list to build SQL to execute, for example:
CREATE or REPLACE FUNCTION public.test2(
IN _sfieldlist varchar)
RETURNS SETOF pg_catalog.record AS
$BODY$
DECLARE
v_feed RECORD;
v_sfieldlist varchar(512);
BEGIN
v_sfieldlist := _sfieldlist;
FOR v_feed IN EXECUTE '
SELECT '||v_sfieldlist||'
FROM feed'
LOOP
RETURN NEXT v_feed;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE SECURITY DEFINER;
This works just ducky from PL/PG SQL when I run a select something like the
following:
select * from test2('feedid') as (name bigint);
However, when I make a straight call to this function from .NET (Core Lab's
Postgres data provider), we get a runtime error (similar to the error you
would get if you ran the following: select * from test2('feedid')).
The idea is to create one generic search which build's the select to execute
from metadata - the catch is that the select list could be anything and this
gets built at runtime meaning that we don't want to have the RETURN type of
the function to be TYPE, TABLE or VIEW in order to maximize flexibility and
re-use.
All help is appreciated.
Thanks,
David
--
View this message in context:
http://www.nabble.com/Function-which-returns-record-tf2961907.html#a8286937
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] pg_standby testing notes
On Thu, 2007-01-11 at 14:20 -0500, Merlin Moncure wrote: I am looking into using pg_standby (v3) in a warm standby system. I'm going to double check it, but same machine replication seemed to work ok. When I tried to do remote server log shipping however, I had some issues. Initial setup and launch is working ok, my archive command is: 'test ! -f /var/lib/pgsql/pitr/%f cp %p /var/lib/pgsql/pitr/%f' my restore command is: pg_standby -m -t/raid/pitr/kill /raid/pitr %f %p '/raid/pitr' is an nfs mount mounted from the primary to the standby. I suspect the problem lies there, but I thought I'd ask. here is my log on the standby following a fresh pitr load: 2007-01-11 07:40:37 EST : LOG: automatic recovery in progress 2007-01-11 07:40:37 EST : LOG: redo starts at 0/63B0 2007-01-11 07:41:37 EST : LOG: restored log file 00010064 from archive 2007-01-11 07:41:38 EST : LOG: restored log file 00010065 from archive 2007-01-11 07:41:38 EST : LOG: restored log file 00010066 from archive 2007-01-11 07:51:44 EST : LOG: could not open file pg_xlog/00010067 (log file 0, segment 103): No such file or directory 2007-01-11 07:51:44 EST : LOG: redo done at 0/66C8 2007-01-11 08:01:49 EST : PANIC: could not open file pg_xlog/00010066 (log file 0, segment 102): No such file or directory neither the primary or the standby are in production...the 3 restored files were from me manufacturing work on the primary to generate files. Is there anything obvious I should be checking? All log lines are normal, apart from the PANIC... This looks familiar to me; I see from my notes that I wanted to exclude -m option, but I left it in. On review, I can't see how -m would work at all without a (minor) change to the backend - it works... apart from the very last file request. Can you try -l and see if that works instead? It should perform the same, roughly. Thanks for your feedback. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Question - Query based on WHERE OR
Mike Poe wrote:
SELECT foo, baz, bar FROM public.table WHERE lastname ~*
'$lastname' OR ssn='$ssn'
I need to leave the last name a wildcard in case someone enters a
partial name, lower case / upper case, etc.
I want the SSN to match exactly if they search by that.
The way it's written, if I enter nothing for the last name, it
returns all rows, regardless of what I enter for the ssn.
I understand why it happens, but what can I do to return the
desired results?
How about:
SELECT foo, baz, bar FROM public.table
WHERE ('$lastname' != '' and lastname ~* '$lastname') OR ssn='$ssn';
Or perhaps some more general condition for empty lastname parameters.
- John Burger
MITRE
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Question - Query based on WHERE OR
You could have your program check to see if the lastname form field was
empty, and send different queries to the database depending on what they
entered.
I'm a perl person, not php, so my php syntax might not be perfect, but
you'll get the idea:
if ($lastname ==) {
$query=SELECT foo, baz, bar FROM public.table WHERE ssn='$ssn';
} elsif (($lastname != ) and ($ssn != )) {
$query=SELECT foo, baz, bar FROM public.table WHERE lastname ~*
'$lastname' OR ssn='$ssn';
}
then execute the query
or, get fancy, and build the where clause:
if ($lastname !=) {
$where.= lastname ~* '$lastname';
}
if ($ssn != ) {
if ($where != ) {
$where.= OR ;
}
$where.= ssn='$ssn';
}
$query=SELECT foo, baz, bar FROM public.table WHERE $where;
Then, run the query.
Just a couple of ideas.
Susan Cassidy
Mike Poe [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
01/10/2007 05:38 PM
To
pgsql-general@postgresql.org
cc
Subject
[GENERAL] Question - Query based on WHERE OR
I'm a rank newbie to Postgres am having a hard time getting my arms
around this.
I'm trying to construct a query to be run in a PHP script. I have an
HTML form were someone can enter either a last name or a social
security number then query the database based on what they entered.
My query looks like this:
SELECT foo, baz, bar FROM public.table WHERE lastname ~* '$lastname' OR
ssn='$ssn'
I need to leave the last name a wildcard in case someone enters a
partial name, lower case / upper case, etc.
I want the SSN to match exactly if they search by that.
The way it's written, if I enter nothing for the last name, it returns
all rows, regardless of what I enter for the ssn.
I understand why it happens, but what can I do to return the desired
results?
Thank you.
Mike
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
--
Simply protected storage solutions ensure that your information is
automatically safe, readily available and always there, visit us at
http://www.overlandstorage.com
--
Re: [GENERAL] Checkpoint request failed on version 8.2.1.
Patrick Earl [EMAIL PROTECTED] writes: In any case, the unit tests remove all contents and schema within the database before starting, and they remove the tables they create as they proceed. Certainly there are many things have been recently deleted. Yeah, I think then there's no question that the bgwriter is trying to fsync something that's been deleted but isn't yet closed by every process. We have things set up so that that's not a really serious problem anymore --- eventually it will be closed and then the next checkpoint will succeed. But CREATE DATABASE insists on checkpointing and so it's vulnerable to even a transient failure. I've been resisting changing the checkpoint code to treat EACCES as a non-error situation on Windows, but maybe we have no choice. How do people feel about this idea: #ifdef WIN32 and the open or fsync fails with EACCES, then 1. Emit a LOG (or maybe DEBUG) message noting the problem. 2. Leave the fsync request entry in the hashtable for next time. 3. Allow the current checkpoint to complete normally anyway. If the file has actually been deleted, then eventually it will be closed and the next checkpoint will be able to remove the hash entry. If there's something else wrong, we'll keep bleating and maybe the DBA will notice eventually. The downside of this is that a real EACCES problem wouldn't get noted at any level higher than LOG, and so you could theoretically lose data without much warning. But I'm not seeing anything else we could do about it --- AFAIK we have not heard of a way we can distinguish this case from a real permissions problem. And anyway there should never *be* a real permissions problem; if there is then the user's been poking under the hood sufficient to void the warranty anyway ;-) Comments? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [HACKERS] [GENERAL] Checkpoint request failed on version 8.2.1.
Tom Lane wrote: Patrick Earl [EMAIL PROTECTED] writes: In any case, the unit tests remove all contents and schema within the database before starting, and they remove the tables they create as they proceed. Certainly there are many things have been recently deleted. Yeah, I think then there's no question that the bgwriter is trying to fsync something that's been deleted but isn't yet closed by every process. We have things set up so that that's not a really serious problem anymore --- eventually it will be closed and then the next checkpoint will succeed. But CREATE DATABASE insists on checkpointing and so it's vulnerable to even a transient failure. I've been resisting changing the checkpoint code to treat EACCES as a non-error situation on Windows, but maybe we have no choice. How do people feel about this idea: #ifdef WIN32 and the open or fsync fails with EACCES, then 1. Emit a LOG (or maybe DEBUG) message noting the problem. 2. Leave the fsync request entry in the hashtable for next time. 3. Allow the current checkpoint to complete normally anyway. If the file has actually been deleted, then eventually it will be closed and the next checkpoint will be able to remove the hash entry. If there's something else wrong, we'll keep bleating and maybe the DBA will notice eventually. The downside of this is that a real EACCES problem wouldn't get noted at any level higher than LOG, and so you could theoretically lose data without much warning. But I'm not seeing anything else we could do about it --- AFAIK we have not heard of a way we can distinguish this case from a real permissions problem. And anyway there should never *be* a real permissions problem; if there is then the user's been poking under the hood sufficient to void the warranty anyway ;-) Comments? I find it very unlikely that you would during normal operations end up in a situation where you would first have permissions to create files in a directory, and then lose them. What could be is that you have a directory where you never had permissions to create the file in the first place. Any chance to differentiate between these? In the first case, someone did something to change the permissions, and can be expected to actually check that things continued to work after that. In the second case, it would be nice if it was possible to catch it faster. //Magnus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [GENERAL] Checkpoint request failed on version 8.2.1.
Magnus Hagander [EMAIL PROTECTED] writes: I find it very unlikely that you would during normal operations end up in a situation where you would first have permissions to create files in a directory, and then lose them. What could be is that you have a directory where you never had permissions to create the file in the first place. Any chance to differentiate between these? The cases we're concerned about involve access to an existing file, not attempts to create a new one, so I'm not clear what your point is. I would certainly *love* to differentiate between these failures and ordinary permissions failures, but so far as I've heard we can't. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [GENERAL] Checkpoint request failed on version 8.2.1.
On Thu, Jan 11, 2007 at 03:14:37PM -0500, Tom Lane wrote: The downside of this is that a real EACCES problem wouldn't get noted at any level higher than LOG, and so you could theoretically lose data without much warning. But I'm not seeing anything else we could do about it --- AFAIK we have not heard of a way we can distinguish this case from a real permissions problem. And anyway there should never *be* a real permissions problem; if there is then the user's been poking under the hood sufficient to void the warranty anyway ;-) Or some other helpful process such as a virus scanner has been poking under the hood for you... :( Given that this could result in data loss, if this was to be done I'd very much want to see a way to disable it in a production environment. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [GENERAL] Checkpoint request failed on version 8.2.1.
Jim C. Nasby [EMAIL PROTECTED] writes: Given that this could result in data loss, if this was to be done I'd very much want to see a way to disable it in a production environment. Production environments are the same ones that won't be happy with random checkpoint failures, either. If we can't find a way to positively identify the deleted-file failures then I think we've got to do something like this. (You know, of course, that my opinion is that no sane person would run a production database on Windows in the first place. So the data-loss risk to me seems less of a problem than the unexpected-failures problem. It's not like there aren't a ton of other data-loss scenarios in that OS that we can't do anything about...) regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [GENERAL] Checkpoint request failed on version 8.2.1.
Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: I find it very unlikely that you would during normal operations end up in a situation where you would first have permissions to create files in a directory, and then lose them. What could be is that you have a directory where you never had permissions to create the file in the first place. Any chance to differentiate between these? The cases we're concerned about involve access to an existing file, not attempts to create a new one, so I'm not clear what your point is. Well, then I don't see it as being a big problem, which was the question, I think. If pgsql had permissions to create the file, it would never lose it unless the dba changed something - and if the dba changed something, then he should check his logs afterwards to make sure he didn't break anything. My point is that if we know that *we* could create the file, than the probability of it being an *actual* permissions problem is very low during normal operations. So it's most likely the delete issue, and thus doing what you propose does seem like a fairly safe bet. I would certainly *love* to differentiate between these failures and ordinary permissions failures, but so far as I've heard we can't. Right, that's the base problem. //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [GENERAL] Checkpoint request failed on version 8.2.1.
On Thu, Jan 11, 2007 at 04:32:42PM -0500, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: Given that this could result in data loss, if this was to be done I'd very much want to see a way to disable it in a production environment. Production environments are the same ones that won't be happy with random checkpoint failures, either. Maybe I'm not understanding what happens in a checkpoint failure, but I'd rather have my pg_xlog fill up (hopefully with a lot af WARNINGS thrown before-hand) and face a long recovery than lose data... If we can't find a way to positively identify the deleted-file failures then I think we've got to do something like this. (You know, of course, that my opinion is that no sane person would run a production database on Windows in the first place. So the data-loss risk to me seems less of a problem than the unexpected-failures problem. It's not like there aren't a ton of other data-loss scenarios in that OS that we can't do anything about...) Yeah, and I share your opinion. Unfortunately, a lot of others do not. :( It would be useful if we had a page somewhere that explained in detail what these data-loss issues were and why they're out of our control. At least then people would (or could...) understand why production + Windows == BadIdea. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [GENERAL] Checkpoint request failed on version 8.2.1.
On Thu, 11 Jan 2007, Tom Lane wrote: ...snip... (You know, of course, that my opinion is that no sane person would run a production database on Windows in the first place. So the data-loss risk to me seems less of a problem than the unexpected-failures problem. It's not like there aren't a ton of other data-loss scenarios in that OS that we can't do anything about...) regards, tom lane PLEASE OH PLEASE document every f-ing one of them! (And I don't mean document Windows issues as comments in the source code. Best would be in the official documentation/on a web page.) On occasion, I could *really* use such a list! (If such already exists, please point me at it!) Thing is, Tom, not everybody has the same level of information you have on the subject... Regards, Richard -- Richard Troy, Chief Scientist Science Tools Corporation 510-924-1363 or 202-747-1263 [EMAIL PROTECTED], http://ScienceTools.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [GENERAL] Checkpoint request failed on version 8.2.1.
Richard Troy wrote: On Thu, 11 Jan 2007, Tom Lane wrote: ...snip... (You know, of course, that my opinion is that no sane person would run a production database on Windows in the first place. So the data-loss risk to me seems less of a problem than the unexpected-failures problem. It's not like there aren't a ton of other data-loss scenarios in that OS that we can't do anything about...) PLEASE OH PLEASE document every f-ing one of them! (And I don't mean document Windows issues as comments in the source code. Best would be in the official documentation/on a web page.) On occasion, I could *really* use such a list! (If such already exists, please point me at it!) Thing is, Tom, not everybody has the same level of information you have on the subject... Please don't. At least not on the PostgreSQL web site nor in the docs. And no, I don't run my production servers on Windows either. For good or ill, we made a decision years ago to do a proper Windows port. I think that it's actually worked out reasonably well. All operating systems have warts. Not long ago I tended to advise people not to run mission critical Postgresql on Linux unless they were *very* careful, due to the over-commit issue. In fact, I don't trust any OS. I use dumps and backups and replication to protect myself from them all. In the present instance, the data loss risk is largely theoretical, as I understand it, as we don't expect a genuine EACCESS error. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [HACKERS] [GENERAL] Checkpoint request failed on version 8.2.1.
Please don't. At least not on the PostgreSQL web site nor in the docs. And no, I don't run my production servers on Windows either. It does seem like it might be a good idea to have FAQs based on each OS, yes? There are various things that effect each OS differently. The most obvious to me being shared memory and wal_sync_method. If could be a good idea to have. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [GENERAL] Checkpoint request failed on version 8.2.1.
On Thu, Jan 11, 2007 at 03:12:07PM -0800, Joshua D. Drake wrote: It does seem like it might be a good idea to have FAQs based on each OS, yes? There are various things that effect each OS differently. The most obvious to me being shared memory and wal_sync_method. If could be a good idea to have. Joshua D. Drake +1 regards, J ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [GENERAL] Checkpoint request failed on version 8.2.1.
Joshua D. Drake wrote: Please don't. At least not on the PostgreSQL web site nor in the docs. And no, I don't run my production servers on Windows either. It does seem like it might be a good idea to have FAQs based on each OS, yes? There are various things that effect each OS differently. The most obvious to me being shared memory and wal_sync_method. But we have per-platform FAQs. If there is information missing, the reason is that nobody has submitted an appropriate patch, nothing more. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] Checkpoint request failed on version 8.2.1.
On Thu, Jan 11, 2007 at 09:42:38PM -0300, Alvaro Herrera wrote: But we have per-platform FAQs. If there is information missing, the reason is that nobody has submitted an appropriate patch, nothing more. where are these FAQs, and why were they not easily found when the original poster sent his email? is there some SEO we need to do on the websites to make things more obvious? regards, J ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [HACKERS] [GENERAL] Checkpoint request failed on version 8.2.1.
On Thu, 2007-01-11 at 21:42 -0300, Alvaro Herrera wrote: Joshua D. Drake wrote: Please don't. At least not on the PostgreSQL web site nor in the docs. And no, I don't run my production servers on Windows either. It does seem like it might be a good idea to have FAQs based on each OS, yes? There are various things that effect each OS differently. The most obvious to me being shared memory and wal_sync_method. But we have per-platform FAQs. If there is information missing, the reason is that nobody has submitted an appropriate patch, nothing more. Yes you are correct, now that I look. It is not obviously apparent though and they do appear to be quite out of date. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] documentation vs reality: template databases
Richard P. Welty wrote: running 8.1 on a fedora core 5 linux box, up to date so far as i know. this page: http://www.postgresql.org/docs/8.1/interactive/manage-ag-templatedbs.html says the following: Note: template1 and template0 do not have any special status beyond the fact that the name template1 is the default source database name for CREATE DATABASE. For example, one could drop template1 and recreate it from template0 without any ill effects. This course of action might be advisable if one has carelessly added a bunch of junk in template1. having just carelessly loaded a backup into template1 instead of the place i wanted to load it, i had a chance to try to follow this advice: foo=# drop database template1; ERROR: cannnot drop a template database foo=# This was written a while ago, but this gives you pretty detailed instructions about how to fix this: http://www.postgresql.org/docs/techdocs.22 -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Corrupt database? 8.1/FreeBSD6.0
PostgreSQL 8.1.2 on i386-portbld-freebsd6.0, compiled by GCC cc (GCC) 3.4.4 [FreeBSD] 20050518 Started seeing this in the logs this afternoon...scaring the begeezus out of me. Jan 11 19:20:19 prod-app-1 postgres[1752]: [5-1] 1752 ERROR: could not access status of transaction 1924023481 Jan 11 19:20:19 prod-app-1 postgres[1752]: [5-2] 1752 DETAIL: could not open file pg_clog/072A: No such file or directory Jan 11 19:24:35 prod-app-1 postgres[4094]: [5-1] 4094 ERROR: could not access status of transaction 1924023481 Jan 11 19:24:35 prod-app-1 postgres[4094]: [5-2] 4094 DETAIL: could not open file pg_clog/072A: No such file or directory Jan 11 19:28:35 prod-app-1 postgres[6728]: [5-1] 6728 ERROR: could not access status of transaction 1924023481 Jan 11 19:28:35 prod-app-1 postgres[6728]: [5-2] 6728 DETAIL: could not open file pg_clog/072A: No such file or directory I could find nothing any transaction refereced in pg_locks...nor could I find a file with that designation (was always the same transaction id) ls -l /db/pg_clog/ total 984 -rw--- 1 pgsql wheel 262144 Jan 11 09:55 07CF -rw--- 1 pgsql wheel 262144 Jan 11 13:45 07D0 -rw--- 1 pgsql wheel 262144 Jan 11 17:00 07D1 -rw--- 1 pgsql wheel 172032 Jan 11 20:39 07D2 At first I thought it was related to a constantly running batch process which I halted, and sure enough, the problem seemed to go away. If I restarted, the problem returned. I then tried select * from on most of the tables used by that process (at least the last days worth) and found no obvious issues or errors. About that time, I noticed that the errors were all IMMEDIATELY preceeded by an autovacuum of template0. So the logs actually looked like this: Jan 11 19:20:19 prod-app-1 postgres[1752]: [4-1] 1752 LOG: autovacuum: processing database template0 Jan 11 19:20:19 prod-app-1 postgres[1752]: [5-1] 1752 ERROR: could not access status of transaction 1924023481 Jan 11 19:20:19 prod-app-1 postgres[1752]: [5-2] 1752 DETAIL: could not open file pg_clog/072A: No such file or directory Jan 11 19:24:35 prod-app-1 postgres[4094]: [4-1] 4094 LOG: autovacuum: processing database template0 Jan 11 19:24:35 prod-app-1 postgres[4094]: [5-1] 4094 ERROR: could not access status of transaction 1924023481 Jan 11 19:24:35 prod-app-1 postgres[4094]: [5-2] 4094 DETAIL: could not open file pg_clog/072A: No such file or directory Jan 11 19:28:35 prod-app-1 postgres[6728]: [4-1] 6728 LOG: autovacuum: processing database template0 Jan 11 19:28:35 prod-app-1 postgres[6728]: [5-1] 6728 ERROR: could not access status of transaction 1924023481 Jan 11 19:28:35 prod-app-1 postgres[6728]: [5-2] 6728 DETAIL: could not open file pg_clog/072A: No such file or directory We've downloaded/compiled pg_filedump, but are stumped as to what relation (or even what database) to start with. I restarted the batch process that I thought was the culprit and the issue has 'gone away'. I'm sure there is some corruption somewhere (from reading various similar posts), but not sure where to start. Any help would be appreciated. - Access over 1 million songs - Yahoo! Music Unlimited.
Re: [GENERAL] Corrupt database? 8.1/FreeBSD6.0
Looking backwards in the logs we see it a few other times this month... (Autovacuum occurring just prior)...same transaction ID How could it be the same transaction ID from several days prior? Jan 2 03:05:04 prod-app-1 postgres[8524]: [4-1] 8524 LOG: autovacuum: processing database template0 Jan 2 03:05:05 prod-app-1 postgres[8524]: [5-1] 8524 ERROR: could not access status of transaction 1924023481 Jan 2 03:05:05 prod-app-1 postgres[8524]: [5-2] 8524 DETAIL: could not open file pg_clog/072A: No such file or directory Jeff Amiel [EMAIL PROTECTED] wrote: PostgreSQL 8.1.2 on i386-portbld-freebsd6.0, compiled by GCC cc (GCC) 3.4.4 [FreeBSD] 20050518 Started seeing this in the logs this afternoon...scaring the begeezus out of me. Jan 11 19:20:19 prod-app-1 postgres[1752]: [5-1] 1752 ERROR: could not access status of transaction 1924023481 Jan 11 19:20:19 prod-app-1 postgres[1752]: [5-2] 1752 DETAIL: could not open file pg_clog/072A: No such file or directory Jan 11 19:24:35 prod-app-1 postgres[4094]: [5-1] 4094 ERROR: could not access status of transaction 1924023481 Jan 11 19:24:35 prod-app-1 postgres[4094]: [5-2] 4094 DETAIL: could not open file pg_clog/072A: No such file or directory Jan 11 19:28:35 prod-app-1 postgres[6728]: [5-1] 6728 ERROR: could not access status of transaction 1924023481 Jan 11 19:28:35 prod-app-1 postgres[6728]: [5-2] 6728 DETAIL: could not open file pg_clog/072A: No such file or directory I could find nothing any transaction refereced in pg_locks...nor could I find a file with that designation (was always the same transaction id) ls -l /db/pg_clog/ total 984 -rw--- 1 pgsql wheel 262144 Jan 11 09:55 07CF -rw--- 1 pgsql wheel 262144 Jan 11 13:45 07D0 -rw--- 1 pgsql wheel 262144 Jan 11 17:00 07D1 -rw--- 1 pgsql wheel 172032 Jan 11 20:39 07D2 At first I thought it was related to a constantly running batch process which I halted, and sure enough, the problem seemed to go away. If I restarted, the problem returned. I then tried select * from on most of the tables used by that process (at least the last days worth) and found no obvious issues or errors. About that time, I noticed that the errors were all IMMEDIATELY preceeded by an autovacuum of template0. So the logs actually looked like this: Jan 11 19:20:19 prod-app-1 postgres[1752]: [4-1] 1752 LOG: autovacuum: processing database template0 Jan 11 19:20:19 prod-app-1 postgres[1752]: [5-1] 1752 ERROR: could not access status of transaction 1924023481 Jan 11 19:20:19 prod-app-1 postgres[1752]: [5-2] 1752 DETAIL: could not open file pg_clog/072A: No such file or directory Jan 11 19:24:35 prod-app-1 postgres[4094]: [4-1] 4094 LOG: autovacuum: processing database template0 Jan 11 19:24:35 prod-app-1 postgres[4094]: [5-1] 4094 ERROR: could not access status of transaction 1924023481 Jan 11 19:24:35 prod-app-1 postgres[4094]: [5-2] 4094 DETAIL: could not open file pg_clog/072A: No such file or directory Jan 11 19:28:35 prod-app-1 postgres[6728]: [4-1] 6728 LOG: autovacuum: processing database template0 Jan 11 19:28:35 prod-app-1 postgres[6728]: [5-1] 6728 ERROR: could not access status of transaction 1924023481 Jan 11 19:28:35 prod-app-1 postgres[6728]: [5-2] 6728 DETAIL: could not open file pg_clog/072A: No such file or directory We've downloaded/compiled pg_filedump, but are stumped as to what relation (or even what database) to start with. I restarted the batch process that I thought was the culprit and the issue has 'gone away'. I'm sure there is some corruption somewhere (from reading various similar posts), but not sure where to start. Any help would be appreciated. - Access over 1 million songs - Yahoo! Music Unlimited. - Check out the all-new Yahoo! Mail beta - Fire up a more powerful email and get things done faster.
Re: [GENERAL] RESTORE Error
Jeanna Geier [EMAIL PROTECTED] writes: And log file shows the following (what I posted before was through pgAdmin): 2007-01-11 15:38:47 ERROR: relation temp_measurement does not exist 2007-01-11 15:38:47 ERROR: syntax error at or near 7094982 at character 1 2007-01-11 15:38:47 ERROR: syntax error at or near 67459 at character 1 OK, that makes it come a bit into focus. I think what pg_restore tried to send was COPY temp_measurement FROM STDIN; 7094982 ... rest of row 1 ... 67459 ... rest of row 2 ... and the COPY command failed because of the stated reason, and then the backend found itself chewing on a lot of data that was being sent as SQL commands (because back then pg_restore depended on feedback from the backend to tell whether an archive entry was COPY data or SQL commands). So the bottom line here is you're trying to restore into a table that's not there. How come? I suppose you were trying a data-only restore, else this is a pg_dump or pg_restore bug. Another point is that AFAICT 8.0 pg_restore does know enough to stop if the COPY command fails --- least the case seems to work for me. [ digs in CVS logs... ] Oh, it seems we fixed that in 8.0.7: 2006-02-05 15:59 tgl * src/bin/pg_dump/: pg_backup_archiver.c, pg_backup_archiver.h, pg_backup_db.c (REL8_0_STABLE): Fix pg_restore to properly discard COPY data when trying to continue after an error in a COPY statement. Formerly it thought the COPY data was SQL commands, and got quite confused. Stephen Frost So I suppose you're running some earlier 8.0.x subrelease, in which case an update would be an extremely good idea. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [GENERAL] Checkpoint request failed on version 8.2.1.
Jim C. Nasby [EMAIL PROTECTED] writes: On Thu, Jan 11, 2007 at 03:14:37PM -0500, Tom Lane wrote: ... And anyway there should never *be* a real permissions problem; if there is then the user's been poking under the hood sufficient to void the warranty anyway ;-) Or some other helpful process such as a virus scanner has been poking under the hood for you... :( One point worth making is that I'm not really convinced anymore that we have proof that antivirus code has been creating any such problems. We have several anecdotal cases where someone reported erratic permission denied problems on Windows, and we suggested getting rid of any AV code, and it seemed to fix their problem --- but how long did they test? This problem is inherently very timing-sensitive, and so the fact that you don't see it for a little while is hardly proof that it's gone. See the report that started this thread for examples of apparent correlations that are really quite spurious, like whether the test case is being driven locally or not. It could easy be that every report we've heard really traces to the not-yet-deleted-file problem. So basically what we'd have is that if you manually remove permissions on a database file or directory you'd be risking data loss; but heck, if you manually move, rename, delete such a file you're risking (guaranteeing) data loss. Any sane user is going to figure keep your fingers away from the moving parts; or if he can't figure that out, he's got no one but himself to blame. It's not ideal, granted, but we're dealing with a much-less-than-ideal OS, so we gotta make some compromises. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Corrupt database? 8.1/FreeBSD6.0
Jeff Amiel [EMAIL PROTECTED] writes: PostgreSQL 8.1.2 on i386-portbld-freebsd6.0, compiled by GCC cc (GCC) 3.4.4 [FreeBSD] 20050518 Jan 2 03:05:04 prod-app-1 postgres[8524]: [4-1] 8524 LOG: autovacuum: processing database template0 Jan 2 03:05:05 prod-app-1 postgres[8524]: [5-1] 8524 ERROR: could not access status of transaction 1924023481 Jan 2 03:05:05 prod-app-1 postgres[8524]: [5-2] 8524 DETAIL: could not open file pg_clog/072A: No such file or directory ls -l /db/pg_clog/ total 984 -rw--- 1 pgsql wheel 262144 Jan 11 09:55 07CF -rw--- 1 pgsql wheel 262144 Jan 11 13:45 07D0 -rw--- 1 pgsql wheel 262144 Jan 11 17:00 07D1 -rw--- 1 pgsql wheel 172032 Jan 11 20:39 07D2 So apparently there's some row in template0 that didn't get marked committed before the pg_clog segment for it went away. Given 8.1's rather schizophrenic view of whether it can modify template0 or not, this is not too surprising, but I thought we'd put in some defense against this happening. Alvaro? Jeff, had you changed your autovac settings recently? Given that autovac seems to be trying to hit template0 every few minutes, it's hard to see how the tuple got missed for long enough to be a problem. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Corrupt database? 8.1/FreeBSD6.0
Jeff Amiel [EMAIL PROTECTED] writes: We've downloaded/compiled pg_filedump, but are stumped as to what relation (or even what database) to start with. Turn up log_min_messages to DEBUG2 and you'll be able to see which table autovac is failing at. If I had to bet I'd bet on template0.pg_statistic ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Select Query
Hello List, I am having list of tables , what I want to do is to filter this list of table for a particular value of its column, the column which i will be searching is common accross all tables in list any clues?? With regards Ashish Karalkar Don't get soaked. Take a quick peak at the forecast with the Yahoo! Search weather shortcut. http://tools.search.yahoo.com/shortcuts/#loc_weather ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Select Query
Ashish Karalkar wrote: Hello List, I am having list of tables , what I want to do is to filter this list of table for a particular value of its column, the column which i will be searching is common accross all tables in list any clues?? Something like SELECT * FROM ( SELECT col1,col2 FROM table1 UNION SELECT col1,col2 FROM table2 UNION SELECT col1,col2 FROM table3 ) AS at WHERE at.col1=3 if the cols are different names you would change SELECT col1,col2 to SELECT col3 as col1,col2 If you are looking for the table that has the value you will want something like - SELECT c.relname,* FROM ( SELECT tableoid,col1,col2 FROM table1 UNION SELECT tableoid,col1,col2 FROM table2 UNION SELECT tableoid,col1,col2 FROM table3 ) as at left join pg_class c on at.tableoid=c.oid WHERE at.col1=3 -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
