[GENERAL] Database Recovery
Hi ! I have a big problem : all my datas are unavailable. When I do a vaccuum, that ok there are rows. When I do a "SELECT" or "\d", nothing. One of you told me that could be that I reached the max transaction ID. My pg_log is very big (1 Go !!) How could I retrieve my datas ? Of course I tried pg_dump : nothing ! Fils is the DB sub directories seems ok... I tried to make a new install, I re create all the DB, than moved my datafiles to each subdirectories. But nothing ! Then I moved to the old pg_log file to the new DB ! Nothing ! Is there a way to retrieve my datas, even without the indexes ??? thank you ! jean-arthur Le simple fait de passer par la fenetre ne suffit pas a la transformer en porte.
Re: [GENERAL] Re: numeric type and odbc from access 2000
I also use the ODBC driver and have noticed some things that could be improved and some bugs Since it's open source I was thinking of getting the source code and making some changes myself Has anyone done this? My knowledge of C is passable so maybe I could do this? The ODBC driver doesn't seem to be too well supported which is a shame because its the only link PG has with the Windows world if your using any MS app or developing in VB. MC. The problem can be fixed in psql with explicit casting but the windows odbc driver for postgres does not make that determination correctly so update and delete statements from access give errors when there is a float or a numeric/decimal type in any table. And unfortunately, there is no way to adjust the sql statements sent from access to the ODBC to explicitly cast datatypes (or at least I think that's true) This seems to be a major issue if one is using access as a front-end client beacuse any table with float/numeric type will not be updated or deleted. Any idea (from anyone) when there will be an ODBC fix or a fix so the numeric type don't have to be explicitly casted in postgresql. cheers, Shahab Shahab Asgharzadeh, MD Bioinformatics Director, Neurogenetics Lab Assistant Professor of Neurology, Northwestern University Pediatric Clinical Associate, University of Chicago 312-503-4737 "You must be the change you wish to see in the world." - Gandhi -- NOTICE: The information contained in this electronic mail transmission is intended by Convergys Corporation for the use of the named individual or entity to which it is directed and may contain information that is privileged or otherwise confidential. If you have received this electronic mail transmission in error, please delete it from your system without copying or forwarding it, and notify the sender of the error by reply email or by telephone (collect), so that the sender's address records can be corrected.
RE: [GENERAL] Re: ALTER DROP COLUMN
Is there any chance of adding a note about this to the docs? It does not seem to be documented anywhere! Maybe a note in ALTER TABLE (sql-altertable.htm) and changing the FAQ slightly to mention what happen to triggers, views, constraints etc. Regards Ben -Original Message- From: Fred Yankowski [mailto:[EMAIL PROTECTED]] Sent: 12 February 2001 21:41 Cc: [EMAIL PROTECTED] Subject: [GENERAL] Re: ALTER DROP COLUMN I just tried this recommended procedure with a little test. It copied over the Attribute and Type information and the primary key index, but left everything else behind: not-null constraints, defaults, references constraints, check constraints, sequence generated for 'serial' type -- to name a few. So how is this useful for anything beyond a toy database? This inability to drop a column makes it hard to follow an "extreme programming" style of development, where one designs only as much into the database schema as is needed at the moment (DTSTTCPW, YAGNI). When I do that I often find that I need to drop columns (in favor of other columns of different types) as I evolve the schema to support ever more complex applications. PostgreSQL makes this a bit harder than it needs to be. On Mon, Feb 12, 2001 at 12:34:20PM -0800, Brent R. Matzelle wrote: I know it's a pain, but it isn't all that bad. Just rename your table like so: ALTER TABLE old_name RENAME TO another_name Then re-create the old table without the deleted column(s): SELECT col1, col2, col4 INTO TABLE old_name FROM another_name Then delete the old table: DROP TABLE another_name -- Fred Yankowski [EMAIL PROTECTED] tel: +1.630.879.1312 Principal Consultant www.OntoSys.com fax: +1.630.879.1370 OntoSys, Inc 38W242 Deerpath Rd, Batavia, IL 60510, USA
Re: [GENERAL] How to make PostgreSQL JDBC drive get PGTZ?
Quoting Raymond Chui [EMAIL PROTECTED]:
My system time zone (TZ) is set to US Eastern Standard Time is -5 hours
of
GMT time
I set
export PGTZ=GMT
then go to psql do
insert into a_table (a_column) values ('2001-02-08 18:30:00+00');
select a_column from a_table;
will get result exactly what I inserted
2001-02-08 18:30:00+00
But when I used JDBC drive from org.postgresql.Driver to insert the
same
value,
I got
2001-02-08 23:30:00+00
This is the value of GMT time at 18:30 of EST time! Which tell me
the Postgres JDBC drive insert the value in EST time. Why is that?
JDBC ( Java in general) works on a standard timezone so your local date is
converted into GMT (or rather UTC) when it's stored, so what you should use is
getDate() and then use a Calendar object (eg GregorianCalendar) to convert to
your local timezone.
7.1 has the extra methods that can pass a Calendar object implemented (not
fully tested yet, but will be in CVS by the weekend).
There are still a few date/time problems. I'm due in the next few evenings to
start writing the TestCases for Date/Time/Timestamp's so when they are done,
we'll have a firm base to work on (rather than guess work used so far).
I already did
Properties p = new Properties();
p.put("PGTZ", "GMT");
before connect to PostgreSQL server, but no luck!
The JDBC driver only accepts a few properties, and PGTZ isn't one of them, and
never will because Java uses it's own time zone handling.
I'd advise you read up on Calendar to see how timezones are handled.
Peter
--
Peter Mount [EMAIL PROTECTED]
PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/
RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/
RE: [GENERAL] transaction safety
Title: RE: [GENERAL] transaction safety Hi, Well, the number is 'locked', because once it's given to you, that's it, it's gone from the 'list of available numbers' (i.e.: the sequence). However, between the insert, and the read of the ID, if another transaction performs an insert, it does NOT affect the ID that the first transaction reads (i.e.: your ID read in the first transaction IS definitely still safe, it will still read the correct one). AND, the first insert does NOT block the second insert. The second insert could complete and commit before the first one. Does this explain better? MikeA -Original Message- From: DaVinci [mailto:[EMAIL PROTECTED]] Sent: 13 February 2001 08:08 To: Lista PostgreSql Subject: Re: [GENERAL] transaction safety On Mon, Feb 12, 2001 at 10:22:30AM -0500, Tom Lane wrote: DaVinci [EMAIL PROTECTED] writes: On Mon, Feb 12, 2001 at 01:08:01PM -, Michael Ansley wrote: Typically, the insert for a person, and for all the associated addresses would be done in the same transaction so that if the insert for one of the addresses failed, then the whole lot would role back (perhaps a bit extreme, but I think that's what you asked for ;-) I thought it is possible to have different transactions opened and insert data in same table from them. It seems my idea was fault, doesn't it?. In sumary: locks with inserts are for table and not for tuple. If this is not true, tell me details, please :) It's not true. How did you arrive at that conclusion from what Mike said? I'll try to explain. Mike said: in a transaction make an insert and then a read in serial current value. If in gap between those operations occurs another insert from different transaction, then reading serial is not safe. In order to understand this well I have made some basic experiments, freezing a transaction with an insert and making other transaction with an insert to the same table. Second gets frozen until first commit or cancel. That is reason of my last message: locks with inserts are for table and not for tuple. Perhaps I didn't explain myself very well or there is some detail about locks that I don't understand at all. Thanks all for your time. David ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify Nick West - Global Infrastructure Manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com **
Re: [GENERAL] jdbc connection pool settings
Quoting Culley Harrelson [EMAIL PROTECTED]: I'm in the process of implementing connection pooling and the setup I'm using (http://www.javaexchange.com - really slick!) has settings for min # connections and max # connection. Any suggestions on where I should set these values? min=2, max=6? My site will be outside the firewall, open to the public for all to trash. Although you're using JDBC here, this is actually a basic postgres admin problem. The size of your pool would be based on what resources you have available and how much traffic you're expecting. Peter Culley __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ -- Peter Mount [EMAIL PROTECTED] PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/ RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/
Re: [GENERAL] transaction safety
On Tue, Feb 13, 2001 at 09:56:18AM -, Michael Ansley wrote: Hi, Well, the number is 'locked', because once it's given to you, that's it, it's gone from the 'list of available numbers' (i.e.: the sequence). However, between the insert, and the read of the ID, if another transaction performs an insert, it does NOT affect the ID that the first transaction reads (i.e.: your ID read in the first transaction IS definitely still safe, it will still read the correct one). I understand this. AND, the first insert does NOT block the second insert. The second insert could complete and commit before the first one. But I don't know how to reproduce this part. If I have two different sessions of psql connected to same database: psql-1# begin; psql-2# begin; psql-1# insert into foo ...; psql-2# insert into foo ...;- ¡¡¡Frozen!!! psql-1# commit; - psql-2 unfrozen Why I get this?. Does this explain better? Very well, thanks ;) David
RE: [GENERAL] transaction safety
Title: RE: [GENERAL] transaction safety OK, someone want to answer this? I have always been under the impression that Postgres would not block under these circumstances, however, this is clearly blocking, for no apparently good reason. I have just run a test on my own server, and this blocking does not happen. Both sessions run independently until each has committed, then displaying information from the other insert, but definitely not blocking. It works exactly as I would have expected. Anybody??? MikeA -Original Message- From: DaVinci [mailto:[EMAIL PROTECTED]] Sent: 13 February 2001 10:42 To: Lista PostgreSql Subject: Re: [GENERAL] transaction safety On Tue, Feb 13, 2001 at 09:56:18AM -, Michael Ansley wrote: Hi, Well, the number is 'locked', because once it's given to you, that's it, it's gone from the 'list of available numbers' (i.e.: the sequence). However, between the insert, and the read of the ID, if another transaction performs an insert, it does NOT affect the ID that the first transaction reads (i.e.: your ID read in the first transaction IS definitely still safe, it will still read the correct one). I understand this. AND, the first insert does NOT block the second insert. The second insert could complete and commit before the first one. But I don't know how to reproduce this part. If I have two different sessions of psql connected to same database: psql-1# begin; psql-2# begin; psql-1# insert into foo ...; psql-2# insert into foo ...; - ¡¡¡Frozen!!! psql-1# commit; - psql-2 unfrozen Why I get this?. Does this explain better? Very well, thanks ;) David ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify Nick West - Global Infrastructure Manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com **
RE: [GENERAL] Drop a user leaves permissions!
I have seen this before myself, and I agree it is less then optimal. I
would think that it's a place where referential integrity should be used.
Either don't allow you to drop the user until all permissions are removed,
or automatically remove all permissions, cascade delete type of thing.
-Original Message-
From: Trewern, Ben [SMTP:[EMAIL PROTECTED]]
Sent: Tuesday, February 13, 2001 11:10 AM
To: '[EMAIL PROTECTED]'
Subject: [GENERAL] Drop a user leaves permissions!
I'm using PostgreSQL and I've been working with permissions.
I've tried the following:
CREATE USER ben;
GRANT ALL ON table1 TO ben;
DROP USER ben;
The permissions get left to the table as in :
Relation| Access permissions
--
table1| {"=","51=arwR"}
I assume that 51 is the ID of the user who was dropped.
Is this a bug? How should I clean up the permissions?
thanks
Ben
RE: [GENERAL] O'Reilly Conference Proposals
Has anyone submitted anything? -Original Message- From: Bruce Momjian [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, February 13, 2001 2:37 PM To: PostgreSQL-announce; PostgreSQL-general Subject: [GENERAL] O'Reilly Conference Proposals There is less than one week left to submit PostgreSQL proposals for the O'Reilly Open Source Convention in San Diego in July. You can learn more about it at: http://candle.pha.pa.us/oreilly/ -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
[GENERAL] psql segfault
Hello,
(posting here, as I'm not sure this is a bug)
I've run into an interesting issue with v7.0.3's psql (connecting to a
7.0.3 backend, of course): while trying to do password auth, the client
segfaults. It'll connect fine when the db is configured to 'trust' local
connections.
Following the standard "initdb -D ...", "createdb testdb", and
successfully setting the password for user postgres...
strace ./psql testdb -U postgres:
read(0, "test\n", 1024) = 5
ioctl(0, SNDCTL_TMR_STOP, {B38400 opost isig icanon echo ...}) = 0
fstat(1, {st_mode=S_IFCHR|0720, st_rdev=makedev(136, 0), ...}) = 0
old_mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1,
0) = 0x40015000
ioctl(1, TCGETS, {B38400 opost isig icanon echo ...}) = 0
write(1, "\n", 1
) = 1
brk(0x807) = 0x807
socket(PF_UNIX, SOCK_STREAM, 0) = 4
fcntl(4, F_SETFL, O_RDONLY|O_NONBLOCK) = 0
connect(4, {sin_family=AF_UNIX, path="/tmp/.s.PGSQL.5432"}, 20) = 0
select(5, [], [4], NULL, NULL) = 1 (out [4])
rt_sigaction(SIGPIPE, {SIG_IGN}, {SIG_DFL}, 8) = 0
send(4, "\0\0\1(\0\2\0\0testdb\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 296,
0) = 296
rt_sigaction(SIGPIPE, {SIG_DFL}, {SIG_IGN}, 8) = 0
select(5, [4], [], NULL, NULL) = 1 (in [4])
recv(4, "R\0\0\0\4Bc", 16384, 0)= 7
--- SIGSEGV (Segmentation fault) ---
+++ killed by SIGSEGV +++
gdb says:
Program received signal SIGSEGV, Segmentation fault.
0x40019fbe in pg_password_sendauth () from
/usr/local/pgsql/lib/libpq.so.2.1
I haven't ever had a problem with Postgres until today, and I'm quite
stumped with it. All of my installs are default compiles on Slackware 7.1
boxes, running various 2.2 and 2.4 kernels. Any thoughts?
Thanks,
John
# John Madden [EMAIL PROTECTED] ICQ: 2EB9EA
# FreeLists, Free mailing lists for all: http://www.freelists.org
# UNIX Systems Engineer, Ivy Tech State College: http://www.ivy.tec.in.us
# Linux, Apache, Perl and C: All the best things in life are free!
[GENERAL] Order question
Hey guys, another strange question here.. If I query and order by a field and there are duplicate values in that field, what makes one return before the other? Just the first one that PG comes to on the disk is displayed first or is something else looked at to determine the order? Example : hhs=# SELECT ordernum,fieldname,oid from resultstyle WHERE style_id=1001 order by ordernum asc; ordernum | fieldname | oid --+---+- 1 | J.jobtitle| 1197126 1 | J.inv_id | 1197125 2 | J.updatedon | 1197127 3 | J.empinitials | 1197128 What makes the record with j.jobtitle appear above te other, when ordernum is the same? Thanks! -Mitch
[GENERAL] Re: Order question
A further extension of this.. What might I be able to additionally order by so that the most recently updated rows get ordered above everything else (within the order by ordernum).. Using the same example : ordernum | fieldname | oid --+---+- 1 | J.jobtitle| 1197126 1 | J.inv_id | 1197125 2 | J.updatedon | 1197127 3 | J.empinitials | 1197128 I just set the row with j.inv_id to 1, I'd like it to be ordered above the row with j.jobtitle in it -- is that possible? Thanks again! -Mitch - Original Message - From: "Mitch Vincent" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, February 13, 2001 5:54 PM Subject: Order question Hey guys, another strange question here.. If I query and order by a field and there are duplicate values in that field, what makes one return before the other? Just the first one that PG comes to on the disk is displayed first or is something else looked at to determine the order? Example : hhs=# SELECT ordernum,fieldname,oid from resultstyle WHERE style_id=1001 order by ordernum asc; ordernum | fieldname | oid --+---+- 1 | J.jobtitle| 1197126 1 | J.inv_id | 1197125 2 | J.updatedon | 1197127 3 | J.empinitials | 1197128 What makes the record with j.jobtitle appear above te other, when ordernum is the same? Thanks! -Mitch
Re: [GENERAL] Re: numeric type and odbc from access 2000
[EMAIL PROTECTED] wrote: I also use the ODBC driver and have noticed some things that could be improved and some bugs Since it's open source I was thinking of getting the source code and making some changes myself Has anyone done this? I've half(or more) done it. I hope I could commit it in a few days. Regards, Hiroshi Inoue
Re: [GENERAL] Re: Order question
On Tue, Feb 13, 2001 at 05:58:35PM -0500, Mitch Vincent wrote: I just set the row with j.inv_id to 1, I'd like it to be ordered above the row with j.jobtitle in it -- is that possible? make a trigger that updates a time stamp and order by that? mrc -- Mike Castle Life is like a clock: You can work constantly [EMAIL PROTECTED] and be right all the time, or not work at all www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc We are all of us living in the shadow of Manhattan. -- Watchmen
Re: [GENERAL] Re: Order question
Mitch Vincent wrote: A further extension of this.. What might I be able to additionally order by so that the most recently updated rows get ordered above everything else (within the order by ordernum).. Using the same example : ordernum | fieldname | oid --+---+- 1 | J.jobtitle| 1197126 1 | J.inv_id | 1197125 2 | J.updatedon | 1197127 3 | J.empinitials | 1197128 I just set the row with j.inv_id to 1, I'd like it to be ordered above the row with j.jobtitle in it -- is that possible? Add a column with a timestamp. Then, in the update/insert, make the timestamp equal the current time. Then ORDER BY ordernum, timestamp. Or better, modify the other ordernums, as you have an ambiguous situation with two ordernums being equal. Writing that in a single UPDATE would be left as an exercise for the reader :-). -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
Re: [GENERAL] psql segfault
John Madden [EMAIL PROTECTED] writes: I've run into an interesting issue with v7.0.3's psql (connecting to a 7.0.3 backend, of course): while trying to do password auth, the client segfaults. It'll connect fine when the db is configured to 'trust' local connections. Hmm. Maybe some local configuration issue is causing this? I'm just guessing, but we haven't heard many reports like this. Could you recompile libpq and psql with -g, then get a backtrace from the corefile? regards, tom lane
[GENERAL] Re: Re: Order question
Sure, I was just wondering if there was some PG internal value I could use (and I should have said that in my email).. Thanks for the suggestion though, I appreciate it.. -Mitch - Original Message - From: "Mike Castle" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, February 13, 2001 6:18 PM Subject: Re: Re: Order question On Tue, Feb 13, 2001 at 05:58:35PM -0500, Mitch Vincent wrote: I just set the row with j.inv_id to 1, I'd like it to be ordered above the row with j.jobtitle in it -- is that possible? make a trigger that updates a time stamp and order by that? mrc -- Mike Castle Life is like a clock: You can work constantly [EMAIL PROTECTED] and be right all the time, or not work at all www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc We are all of us living in the shadow of Manhattan. -- Watchmen
Re: [GENERAL] SELECT (sometimes) returning Zero Rows?
"Matt Friedman" [EMAIL PROTECTED] writes: We are having what seems like a baffling problem to me. On occasion, our SELECTs will return zero rows when we know with certainty that they should be returning at least some rows. No error occurs, the select just behaves as if the db is empty or something. What PG version is this? What does EXPLAIN show for the misbehaving queries? regards, tom lane
Re: [GENERAL] Re: Order question
"Mitch Vincent" [EMAIL PROTECTED] writes: I just set the row with j.inv_id to 1, I'd like it to be ordered above the row with j.jobtitle in it -- is that possible? Not unless you add a field with an update sequence number, or some such, and then explicitly use that field as a second ORDER BY key. The system does not maintain anything like that for you --- and should not, IMHO, since it'd be useless overhead for apps that didn't care. regards, tom lane
[GENERAL] temporary tables
Since I cannot return a set of values (namely rows from a table) from a function, I thought I could create a temporary table where I could place the resulting information. Unfortunately I have a big problem. If I create this table and run the same procedure again (during the same session) I'll get a message saying the table already exists, but there's no way I can drop this table from plpgsql. Is there a way I can implement this? Thanx.
[GENERAL] Re: SELECT (sometimes) returning Zero Rows?
It's version 7.0.3 Here's the output for the selects using explain below. We had planned to do all of the optimization later. We just haven't had time to get to it yet. Could that be the problem? Anyhow here's the info. Please let me know if you need more information. Appreciatively, Matt Friedman. parent_report_new= explain parent_report_new- SELECT pr_article.title, pr_article.article_id FROM pr_article WHERE parent_report_new- pr_article.article_id = pr_article_age.article_id AND pr_article_age.age_id parent_report_new- = 1 AND pr_article.is_active = true AND pr_article.is_approved = true AND parent_report_new- pr_article.release_date 982093578 AND ((pr_article.end_date = 0) or parent_report_new( (pr_article.end_date 982093578 )) ORDER BY pr_article.release_date DESC parent_report_new- LIMIT 2; NOTICE: QUERY PLAN: Sort (cost=4.39..4.39 rows=1 width=24) - Nested Loop (cost=0.00..4.38 rows=1 width=24) - Seq Scan on pr_article_age (cost=0.00..1.68 rows=1 width=4) - Index Scan using pr_article_pkey on pr_article (cost=0.00..2.03 rows=1 width=20) EXPLAIN parent_report_new= explain parent_report_new- SELECT pr_article.title, pr_article.article_id FROM pr_article WHERE parent_report_new- pr_article.article_id = pr_article_age.article_id AND pr_article_age.age_id parent_report_new- = 1 AND pr_article.is_active = true AND pr_article.is_approved = true AND parent_report_new- pr_article.release_date 982093578 AND ((pr_article.end_date = 0) or parent_report_new( (pr_article.end_date 982093578 )) ORDER BY pr_article.release_date DESC parent_report_new- LIMIT 3; NOTICE: QUERY PLAN: Sort (cost=4.39..4.39 rows=1 width=24) - Nested Loop (cost=0.00..4.38 rows=1 width=24) - Seq Scan on pr_article_age (cost=0.00..1.68 rows=1 width=4) - Index Scan using pr_article_pkey on pr_article (cost=0.00..2.03 rows=1 width=20) EXPLAIN parent_report_new= explain parent_report_new- SELECT DISTINCT ON (release_date, article_id) pr_article.title, parent_report_new- pr_article.article_id, pr_cat.prog_name FROM pr_article WHERE parent_report_new- pr_article.article_id = pr_article_age.article_id AND pr_article_age.age_id parent_report_new- = 1 AND pr_article_cat.cat_id=pr_cat.cat_id AND pr_article.article_id = parent_report_new- pr_article_cat.article_id AND pr_article.is_active = true AND parent_report_new- pr_article.is_approved = true AND pr_article.release_date 982093578 AND parent_report_new- ((pr_article.end_date = 0) or (pr_article.end_date 982093578 )) ORDER BY parent_report_new- pr_article.release_date DESC LIMIT 5; NOTICE: QUERY PLAN: Unique (cost=7.63..7.64 rows=0 width=48) - Sort (cost=7.63..7.63 rows=1 width=48) - Nested Loop (cost=0.00..7.62 rows=1 width=48) - Nested Loop (cost=0.00..5.58 rows=1 width=28) - Nested Loop (cost=0.00..4.36 rows=1 width=12) - Seq Scan on pr_article_age (cost=0.00..1.68 rows=1 width=4) - Index Scan using pr_article_cat_article_id_key on pr_article_cat (cost=0.00..2.01 rows=1 width=8) - Seq Scan on pr_cat (cost=0.00..1.10 rows=10 width=16) - Index Scan using pr_article_pkey on pr_article (cost=0.00..2.03 rows=1 width=20) EXPLAIN parent_report_new= explain parent_report_new- SELECT DISTINCT ON (pr_cat.name) pr_cat.name, pr_cat.prog_name FROM parent_report_new- pr_article, pr_article_age, pr_article_cat, pr_cat WHERE parent_report_new- pr_article_age.age_id = 1 AND pr_article_age.article_id = parent_report_new- pr_article.article_id AND pr_article.article_id = pr_article_cat.article_id parent_report_new- AND pr_article_cat.cat_id = pr_cat.cat_id AND pr_article.is_active = true parent_report_new- AND pr_article.is_audio = false AND pr_article.is_approved = true AND parent_report_new- pr_article.release_date 982093578 AND ((pr_article.end_date = 0) or parent_report_new( (pr_article.end_date 982093578 )) parent_report_new- ; NOTICE: QUERY PLAN: Unique (cost=7.64..7.64 rows=0 width=44) - Sort (cost=7.64..7.64 rows=1 width=44) - Nested Loop (cost=0.00..7.63 rows=1 width=44) - Nested Loop (cost=0.00..6.40 rows=1 width=16) - Nested Loop (cost=0.00..4.36 rows=1 width=12) - Seq Scan on pr_article_age (cost=0.00..1.68 rows=1 width=4) - Index Scan using pr_article_cat_article_id_key on pr_article_cat (cost=0.00..2.01 rows=1 width=8) - Index Scan using pr_article_pkey on pr_article (cost=0.00..2.03 rows=1 width=4) - Seq Scan on pr_cat (cost=0.00..1.10 rows=10 width=28) EXPLAIN - Original Message - From: "Tom Lane" [EMAIL PROTECTED] To: "Matt Friedman" [EMAIL PROTECTED] Cc: "PgSql General List" [EMAIL PROTECTED] Sent: Tuesday, February 13, 2001 3:46 PM Subject: Re: SELECT (sometimes) returning Zero Rows? "Matt Friedman" [EMAIL
Re: [GENERAL] Re: SELECT (sometimes) returning Zero Rows?
On Wed, 14 Feb 2001 15:03, you wrote: It's version 7.0.3 Here's the output for the selects using explain below. We had planned to do all of the optimization later. We just haven't had time to get to it yet. Could that be the problem? Anyhow here's the info. Please let me know if you need more information. This has the same smell about it as the one a couple of days ago where the CR characters in a file loaded into the database using COPY were upsetting things. So, How did you populate your database? -- Sincerely etc., NAME Christopher Sawtell CELL PHONE 021 257 4451 ICQ UIN45863470 EMAIL csawtell @ xtra . co . nz CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz -- Please refrain from using HTML or WORD attachments in e-mails to me --
[GENERAL] Re: Re: SELECT (sometimes) returning Zero Rows?
This has the same smell about it as the one a couple of days ago where the CR characters in a file loaded into the database using COPY were upsetting things. I didn't use COPY but... I used a dump created by pg_dump. We had some problems with the database a while ago so I recreated it using a recent dump file. To reimport it I had psql read it's commands from the file and do it's thing that way. It ran smoothly and I didn't think anything of it. The command I used would have been: $psql -f /path/to/sql/file.sql Could this be causing the problem? If so, how would I go about fixing it? Many thanks, Matt Friedman - Original Message - From: "Christopher Sawtell" [EMAIL PROTECTED] To: "Matt Friedman" [EMAIL PROTECTED] Cc: "PgSql General List" [EMAIL PROTECTED] Sent: Tuesday, February 13, 2001 6:58 PM Subject: Re: Re: SELECT (sometimes) returning Zero Rows? On Wed, 14 Feb 2001 15:03, you wrote: It's version 7.0.3 Here's the output for the selects using explain below. We had planned to do all of the optimization later. We just haven't had time to get to it yet. Could that be the problem? Anyhow here's the info. Please let me know if you need more information. So, How did you populate your database? -- Sincerely etc., NAME Christopher Sawtell CELL PHONE 021 257 4451 ICQ UIN45863470 EMAIL csawtell @ xtra . co . nz CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz -- Please refrain from using HTML or WORD attachments in e-mails to me --
[GENERAL] PostgreSQL non-FAQ documentation
Hi all, I've just started starting a project on Sourceforge to contain PostgreSQL information about known bugs for specific versions, work-arounds, and any other general stuff that doesn't belong in a FAQ. Does anyone have anything they'd like to contribute or similar? At www.sourceforge.net/projects/pgdocs :-) Regards and best wishes, Justin Clift Database Administrator
[GENERAL] Bad book review
I am not sure how many people have looked at my book on Amazon.com, but I have received my first negative book review: http://www.amazon.com/exec/obidos/ASIN/0201703319/o/qid%3D976592762/sr%3D8-1/ref%3Daps%5Fsr%5Fb%5F1%5F3/104-0116316-8891907 I guess everyone isn't going to like my book. :-) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
[GENERAL] Re: Bad book review
I have it and have found it very useful. Matt Friedman Spry New Media - Original Message - From: "Bruce Momjian" [EMAIL PROTECTED] To: "PostgreSQL-general" [EMAIL PROTECTED] Sent: Tuesday, February 13, 2001 11:06 PM Subject: Bad book review I am not sure how many people have looked at my book on Amazon.com, but I have received my first negative book review: http://www.amazon.com/exec/obidos/ASIN/0201703319/o/qid%3D976592762/sr%3D8-1 /ref%3Daps%5Fsr%5Fb%5F1%5F3/104-0116316-8891907 I guess everyone isn't going to like my book. :-) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
[GENERAL] O'Reilly Conference Proposals
There is less than one week left to submit PostgreSQL proposals for the O'Reilly Open Source Convention in San Diego in July. You can learn more about it at: http://candle.pha.pa.us/oreilly/ -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
