Re: [SQL] pg_xlog on separate drive
Hi, Travis, Travis Whitton wrote: > Hey guys, sorry if this is slightly OT for this list, but I figure it's > a simple question. If I'm storing pg_xlog on a second non-redundant > drive using the symlink method and the journal drive were to crash, how > difficult is recovery? Will Postgresql simply be able to reinitialize > the journal on a new drive and carry on, or is there more to it than > that? I realize any pending transactions would be lost, but that's not a > huge concern for me because everything I'm importing comes from raw data. The problem is that you risk inconsistency at data and structural level. When the server crashes, it might happen that some pages in the data files are written only partially (because most disks have a much smaller blocksize than the PostgreSQL page size (which is 8k by default)). Now, when the server cannot reply the WAL log, those half-written pages will not be repaired, and your data may be inconsistent at a very low sematic level (duplicate rows, missing rows, broken rows, backend crashes etc.) with no way to repair. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] pg_xlog on separate drive
Thanks for the replies guys. I think I may be ok in my case though because I'll be importing data as a single daily batch from raw data. I'll be taking nightly backups, and in the event of a crash, I can simply restore from a recent backup and then reimport the raw data. I can now see why losing pg_xlog would be a big problem if I were inserting and updating data continuously throughout the day though. Thanks, Travis On 12/4/06, Markus Schaber <[EMAIL PROTECTED]> wrote: Hi, Travis, Travis Whitton wrote: > Hey guys, sorry if this is slightly OT for this list, but I figure it's > a simple question. If I'm storing pg_xlog on a second non-redundant > drive using the symlink method and the journal drive were to crash, how > difficult is recovery? Will Postgresql simply be able to reinitialize > the journal on a new drive and carry on, or is there more to it than > that? I realize any pending transactions would be lost, but that's not a > huge concern for me because everything I'm importing comes from raw data. The problem is that you risk inconsistency at data and structural level. When the server crashes, it might happen that some pages in the data files are written only partially (because most disks have a much smaller blocksize than the PostgreSQL page size (which is 8k by default)). Now, when the server cannot reply the WAL log, those half-written pages will not be repaired, and your data may be inconsistent at a very low sematic level (duplicate rows, missing rows, broken rows, backend crashes etc.) with no way to repair. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org
[SQL] Using Control Flow Functions in a SELECT Statement
Hi Team I am unable to fetch data using following simple query it prompts following error *ORA: 00907: Missing right parenthesis* Query :- SELECT Name AS Title, StatID AS Status, RatingID AS Rating, IF(NumDisks>1, 'Check for extra disks!', 'Only 1 disk.') AS Verify FROM DVDs *Table Structure :-* CREATE TABLE DVDs ( Name VARCHAR(60) NOT NULL, NumDisks INT NOT NULL , RatingID VARCHAR(4) NOT NULL, StatID CHAR(3) NOT NULL ) rows has been inserted with different numdisks numbers. pls help warm R's
Re: [SQL] Using Control Flow Functions in a SELECT Statement
> *ORA: 00907: Missing right parenthesis* > Query :- > SELECT Name AS Title, StatID AS Status, RatingID AS Rating, > IF(NumDisks>1, 'Check for extra disks!', 'Only 1 disk.') AS Verify What kind of error is ORA? Is this an oracle error? In postgresql I do not believe that the "IF" predicate exists. However the "CASE" predicate does and will do what you want. http://www.postgresql.org/files/documentation/books/aw_pgsql/node44.html Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Using Control Flow Functions in a SELECT Statement
Ashish Ahlawat wrote: Hi Team I am unable to fetch data using following simple query it prompts following error *ORA: 00907: Missing right parenthesis* Query :- SELECT Name AS Title, StatID AS Status, RatingID AS Rating, IF(NumDisks>1, 'Check for extra disks!', 'Only 1 disk.') AS Verify i. That's an Oracle error message. ii. IF does not belong in an SQL query. Use CASE. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Using Control Flow Functions in a SELECT Statement
Hi Team
Thanks FOR your prompt responseBut USING CASE issue still NOT resolved
Oracle prompts same error.
Team its a simple query but really instresting one...
Following data has been updated IN TABLE DVDs
INSERT INTO DVDs (Name, NumDisks, RatingID, StatID)
VALUES('Mash', 2, 'R', 's2'), ('View', 1, 'NR', 's1'), ('Show', 2, 'NR',
's2'), ('Amadeus', 1, 'PG', 's2') , ('Falcon', 1, 'NR', 's2'),
('Africa', 1, 'PG', 's1'), ('Doc', 1, 'G', 's2') , ('Christmas', 1, 'NR',
's1');
On 12/4/06, Bricklen Anderson <[EMAIL PROTECTED]> wrote:
Ashish Ahlawat wrote:
>
> Hi Team
>
> I am unable to fetch data using following simple query it prompts
> following error
>
> *ORA: 00907: Missing right parenthesis*
>
> Query :-
>
> SELECT
>
> Name AS Title, StatID AS Status, RatingID AS Rating,
>
> IF(NumDisks>1, 'Check for extra disks!', 'Only 1 disk.') AS Verify
>
i. That's an Oracle error message.
ii. IF does not belong in an SQL query. Use CASE.
Re: [SQL] Using Control Flow Functions in a SELECT Statement
> Thanks FOR your prompt responseBut USING CASE issue still NOT resolved > Oracle prompts same error. I see, was answers to you get from the oracle mailing lists regarding this problem? ;o) Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] null values in non-nullable column
In 8.1 I have a situation where nullability of user defined datatypes does not seem to be enforced. Using the following steps I end up with a table that has a column that should not be nullable, but has nulls in it. Here's a pared down outline of the steps: -- create a datatype that should enforce not null values gp_test=# create domain boolean_not_null as boolean not null; -- a table with a field using the datatype gp_test=# create table a (id integer, test boolean_not_null); gp_test=# insert into a values (1, true); gp_test=# select * from a; id | test +-- 1 | t -- as expected no nulls are allowed gp_test=# insert into a values (100, null); ERROR: domain boolean_not_null does not allow null values -- a second table (a parent) gp_test=# create table b (id integer); gp_test=# insert into b values (1); gp_test=# insert into b values (2); gp_test=# select * from b; id 1 2 -- now create a table based on a left join -- this creates a table with a not-nullable column (datatype -- inherited from the original table) which contains nulls; -- even though insertion of new nulls is not allowed gp_test=# create table m as select id, test from b left join a using (id); gp_test=# \d m; Table "public.m" Column | Type | Modifiers +--+--- id | integer | test | boolean_not_null | gp_test=# select * from m; id | test + 1 | t 2 | gp_test=# insert into m values (100, null); ERROR: domain boolean_not_null does not allow null values gp_test=# insert into m (id) values (100); ERROR: domain boolean_not_null does not allow null values gp_test=# update m set test = test; -- note no error here! gp_test=# update m set test = (test and true); ERROR: domain boolean_not_null does not allow null values I would have expected failure at the table creation step, but it proceeds (and inserts the nulls). Interestingly, I do see a failure after I try to restore the table from a dump (using pg_dump/pg_restore). George ---(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: [SQL] null values in non-nullable column
"George Pavlov" <[EMAIL PROTECTED]> writes: > -- this creates a table with a not-nullable column (datatype > -- inherited from the original table) which contains nulls; Hm. Arguably we should discard domain-ness in any SELECT result, but I'm sure some people would complain about that ... regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] null values in non-nullable column
On Dec 5, 2006, at 11:52 , Tom Lane wrote: "George Pavlov" <[EMAIL PROTECTED]> writes: -- this creates a table with a not-nullable column (datatype -- inherited from the original table) which contains nulls; Hm. Arguably we should discard domain-ness in any SELECT result, but I'm sure some people would complain about that ... Tom, Could you expand on that a bit? Here's what I've interpreted: The column types of the select are assumed to be (int, boolean_not_null), and so the values aren't checked again before the insert during CREATE TABLE AS. "discarding domain-ness" would mean considering the results as their base type, and rechecking the domain would be checked when inserting into the table. Just trying to fit my head around this. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] null values in non-nullable column
Michael Glaesemann <[EMAIL PROTECTED]> writes: > Could you expand on that a bit? Here's what I've interpreted: > The column types of the select are assumed to be (int, > boolean_not_null), and so the values aren't checked again before the > insert during CREATE TABLE AS. "discarding domain-ness" would mean > considering the results as their base type, and rechecking the domain > would be checked when inserting into the table. To be clear: the problem is not with CREATE TABLE AS. The problem is with the definition of what a SELECT ... LEFT JOIN ... is supposed to return. C.T.A. is supposed to create a table matching the defined column types of the SELECT result; either those types allow null-ness, or they don't. Actually I think this is a bug in the SQL spec :-( The description of says that output columns are "possibly nullable" if they're on the nullable side of the outer join, but it's not apparent that that idea is meant to negate a domain constraint. And yet, if it does not, then an outer join with a NOT NULL domain column on the nullable side is just invalid. 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: [SQL] null values in non-nullable column
On Dec 5, 2006, at 12:49 , Tom Lane wrote: Actually I think this is a bug in the SQL spec :-( The description of says that output columns are "possibly nullable" if they're on the nullable side of the outer join, but it's not apparent that that idea is meant to negate a domain constraint. And yet, if it does not, then an outer join with a NOT NULL domain column on the nullable side is just invalid. Very interesting. Thanks for the explanation. I should dig into the spec more. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
