Re: [GENERAL] Improving Full text performance
On Sat, 22 Aug 2009, xaviergxf wrote: If i strip all html tags and filter more stop words, will the search be more accurate? Actually my fulltext stats returns some like: font from font tags i guess, and other garbage. If i do that, will i improve the speed of my search? What do you mean 'accurate' ? You need be yourself a bit more 'accurate' when asking:) You need to provide more information about your problem. For example, version of postgresql, size of collection you indexed, explain analyze for your query, 'garbage' you got, etc. This is not difficult - just copy'n paste work. Thanks! Ps: I cannot use other tools like MNOsearch, lucene, etc...because i have no root pass to my server. On 22 ago, 02:20, o...@sai.msu.su (Oleg Bartunov) wrote: On Fri, 21 Aug 2009, xaviergxf wrote: Hi, =A0 I?m using php and full text on postgresql 8.3 for indexing html descriptions. I have no acess to postgresql server, since i use a shared hosting service. =A0 =A0To improve search and performance, i want to do the follow: Strip all html tags then use my php script to remove more stop words (because i can?t edit stop words file on the server). My question: What i?m thinking to do, has any collateral effects? Any suggestions? You shouldn't bother to strip all html tags, just create your own text se= arch configuration, which index only what do you want. Read documentation for details. =A0 =A0 =A0 =A0 Regards, =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su,http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) To make changes to your subscription:http://www.postgresql.org/mailpref/p= gsql-general --=20 Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Strange missing tables problem
Hello, I have a strange problem since I moved some tables to a schema, some tables are missing from the list (with \d or \dt) but they are still present anyway ???! Example : $ psql mybase Bienvenue dans psql 8.1.17, l'interface interactive de PostgreSQL. Saisissez: mybase=# bw_rma=# \dt Liste des relations Schéma | Nom| Type | Propriétaire -+--+---+-- import | rebates_products | table | postgres import | rebates_customers| table | postgres rma | categories | table | postgres rma | customers| table | postgres rma | defauts | table | postgres rma | providers| table | postgres No trace of my import.clients table ? But if I do : bw_rma=# SELECT count(*) FROM import.customers; count --- 86703 (1 ligne) My table is there and I can access it !!! Any hint or help would be greatly appreciated ! I can do without it but, it's a little strange not to be able to list the objects present in the database... Denis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Strange missing tables problem
Hello, Small correction to my previous email : I have a strange problem since I moved some tables to a schema, some tables are missing from the list (with \d or \dt) but they are still present anyway ???! Example : $ psql mybase Bienvenue dans psql 8.1.17, l'interface interactive de PostgreSQL. Saisissez: mybase=# bw_rma=# \dt Liste des relations Schéma | Nom| Type | Propriétaire -+--+---+-- import | rebates_products | table | postgres import | rebates_customers| table | postgres rma | categories | table | postgres rma | customers| table | postgres rma | defauts | table | postgres rma | providers| table | postgres No trace of my import.customers table ? But if I do : bw_rma=# SELECT count(*) FROM import.customers; count --- 86703 (1 ligne) My table is there and I can access it !!! Any hint or help would be greatly appreciated ! I can do without it, but it's a little strange not to be able to list the objects present in the database... Denis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Strange missing tables problem
hi, You may try checking: SELECT * FROM pg_catalog.pg_class WHERE relname = 'customers' SELECT * FROM pg_catalog.pg_tables WHERE tablename = 'customers' to what's the status of your table. Regards, foo Denis BUCHER wrote: Hello, Small correction to my previous email : I have a strange problem since I moved some tables to a schema, some tables are missing from the list (with \d or \dt) but they are still present anyway ???! Example : $ psql mybase Bienvenue dans psql 8.1.17, l'interface interactive de PostgreSQL. Saisissez: mybase=# bw_rma=# \dt Liste des relations Schéma | Nom| Type | Propriétaire -+--+---+-- import | rebates_products | table | postgres import | rebates_customers| table | postgres rma | categories | table | postgres rma | customers| table | postgres rma | defauts | table | postgres rma | providers| table | postgres No trace of my import.customers table ? But if I do : bw_rma=# SELECT count(*) FROM import.customers; count --- 86703 (1 ligne) My table is there and I can access it !!! Any hint or help would be greatly appreciated ! I can do without it, but it's a little strange not to be able to list the objects present in the database... Denis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Strange missing tables problem
Hello,
That's what I found, do you see something inside that looks interesting ?
bw_rma=# SELECT * FROM pg_catalog.pg_class WHERE relname = 'customers';
relname | relnamespace | reltype | relowner | relam | relfilenode |
reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid |
relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers
| relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules
| relhassubclass | relacl
-+--+-+--+---+-+---+--+---+---+---+-+-+-+--+---+-+--+--+-+++-++--
customers |17013 | 17022 | 10 | 0 | 17021 |
0 |16202 | 86685 | 0 | 0 | f
| f | r |9 | 0 | 0 |
0 |0 | 0 | f | f | f |
f | {postgres=arwdRxt/postgres,as400=arwdRxt/postgres}
customers |17055 | 16398 | 10 | 0 | 16397 |
0 | 2831 | 80929 | 0 | 0 | t
| f | r |9 | 0 | 0 |
0 |0 | 0 | f | f | f |
f | {postgres=arwdRxt/postgres,rma_php=r/postgres}
(2 lignes)
bw_rma=# SELECT * FROM pg_catalog.pg_tables WHERE tablename = 'customers';
schemaname| tablename | tableowner | tablespace | hasindexes |
hasrules | hastriggers
-+---++++--+-
bw_import_as400 | clients | postgres || f | f
| f
rma | clients | postgres || t | f
| f
(2 lignes)
Thanks a lot for your help :-)
Denis
Wojtek a écrit :
hi,
You may try checking:
SELECT * FROM pg_catalog.pg_class WHERE relname = 'customers'
SELECT * FROM pg_catalog.pg_tables WHERE tablename = 'customers'
to what's the status of your table.
Regards,
foo
Denis BUCHER wrote:
Hello,
Small correction to my previous email :
I have a strange problem since I moved some tables to a schema, some
tables are missing from the list (with \d or \dt) but they are still
present anyway ???!
Example :
$ psql mybase
Bienvenue dans psql 8.1.17, l'interface interactive de PostgreSQL.
Saisissez:
mybase=#
bw_rma=# \dt
Liste des relations
Schéma | Nom| Type | Propriétaire
-+--+---+--
import | rebates_products | table | postgres
import | rebates_customers| table | postgres
rma | categories | table | postgres
rma | customers| table | postgres
rma | defauts | table | postgres
rma | providers| table | postgres
No trace of my import.customers table ?
But if I do :
bw_rma=# SELECT count(*) FROM import.customers;
count
---
86703
(1 ligne)
My table is there and I can access it !!!
Any hint or help would be greatly appreciated !
I can do without it, but it's a little strange not to be able to list
the objects present in the database...
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multiple table entries?
Greg Stark wrote:
On Sun, Aug 23, 2009 at 4:06 AM, Jeff Rossjr...@wykids.org wrote:
Greg Stark wrote:
Actually, I wonder if this isn't more likely to show the problem -- it
would explain why *all* your tables are showing up with duplicates
rather than just one.
select xmin,xmax,ctid,oid,* from pg_namespace
http://openvistas.net/pg_namespace_query.html
Yeah, that's a problem. Would you be able to load the pageinspect
contrib module and run a query?
select (h).* from (select
heap_page_items(get_raw_page('pg_namespace',0)) as h from p) as x;
I had to modify your query slightly to make it run--hope I got what you
are after!
select (h).* from (select
heap_page_items(get_raw_page('pg_namespace',0)) as h) as x;
http://www.openvistas.net/pageinspect.html
Thanks,
Jeff
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Strange missing tables problem
Hello,
It's even more strange :
# \dt customers
Liste des relations
Schéma | Nom | Type | Propriétaire
+-+---+--
rma| customers | table | postgres
(1 ligne)
# \dt import.customers
Liste des relations
Schéma | Nom | Type | Propriétaire
-+-+---+--
import | customers | table | postgres
(1 ligne)
# \dt *.customers
Liste des relations
Schéma | Nom | Type | Propriétaire
-+-+---+--
import | customers | table | postgres
rma | customers | table | postgres
(2 lignes)
I really don't understand what's happening here ?
Denis
That's what I found, do you see something inside that looks interesting ?
# SELECT * FROM pg_catalog.pg_class WHERE relname = 'customers';
relname | relnamespace | reltype | relowner | relam | relfilenode |
reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid |
relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers
| relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules
| relhassubclass | relacl
-+--+-+--+---+-+---+--+---+---+---+-+-+-+--+---+-+--+--+-+++-++--
customers |17013 | 17022 | 10 | 0 | 17021 |
0 |16202 | 86685 | 0 | 0 | f
| f | r |9 | 0 | 0 |
0 |0 | 0 | f | f | f |
f | {postgres=arwdRxt/postgres,as400=arwdRxt/postgres}
customers |17055 | 16398 | 10 | 0 | 16397 |
0 | 2831 | 80929 | 0 | 0 | t
| f | r |9 | 0 | 0 |
0 |0 | 0 | f | f | f |
f | {postgres=arwdRxt/postgres,rma_php=r/postgres}
(2 lignes)
# SELECT * FROM pg_catalog.pg_tables WHERE tablename = 'customers';
schemaname| tablename | tableowner | tablespace | hasindexes |
hasrules | hastriggers
-+---++++--+-
bw_import_as400 | clients | postgres || f | f
| f
rma | clients | postgres || t | f
| f
(2 lignes)
Thanks a lot for your help :-)
Denis
Wojtek a écrit :
hi,
You may try checking:
SELECT * FROM pg_catalog.pg_class WHERE relname = 'customers'
SELECT * FROM pg_catalog.pg_tables WHERE tablename = 'customers'
to what's the status of your table.
Regards,
foo
Denis BUCHER wrote:
Hello,
Small correction to my previous email :
I have a strange problem since I moved some tables to a schema, some
tables are missing from the list (with \d or \dt) but they are still
present anyway ???!
Example :
$ psql mybase
Bienvenue dans psql 8.1.17, l'interface interactive de PostgreSQL.
Saisissez:
mybase=#
bw_rma=# \dt
Liste des relations
Schéma | Nom| Type | Propriétaire
-+--+---+--
import | rebates_products | table | postgres
import | rebates_customers| table | postgres
rma | categories | table | postgres
rma | customers| table | postgres
rma | defauts | table | postgres
rma | providers| table | postgres
No trace of my import.customers table ?
But if I do :
bw_rma=# SELECT count(*) FROM import.customers;
count
---
86703
(1 ligne)
My table is there and I can access it !!!
Any hint or help would be greatly appreciated !
I can do without it, but it's a little strange not to be able to list
the objects present in the database...
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Strange missing tables problem
Possible xid rollover problem? (We saw behavior similar to this during a recent
xid rollover fiasco, where tables didn't appear in the various catalogs and psql
catalog commands, but the data was still there.)
What version of PostgreSQL are you on?
If you try a VACUUM on the pg_catalog.pg_class and pg_catalog.pg_tables tables,
do the proper rows come back?
- Bill
-Original Message-
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
ow...@postgresql.org] On Behalf Of Denis BUCHER
Sent: Sunday, August 23, 2009 8:55 AM
To: Wojtek
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Strange missing tables problem
Hello,
That's what I found, do you see something inside that looks interesting ?
bw_rma=# SELECT * FROM pg_catalog.pg_class WHERE relname = 'customers';
relname | relnamespace | reltype | relowner | relam | relfilenode |
reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid |
relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers
| relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules
| relhassubclass | relacl
-+--+-+--+---+-+
---+--
+---+---+---+-+-+---
--+--
+---+-+--+--+-++
+-
++--
customers |17013 | 17022 | 10 | 0 | 17021 |
0 |16202 | 86685 | 0 | 0 | f
| f | r |9 | 0 | 0 |
0 |0 | 0 | f | f | f |
f | {postgres=arwdRxt/postgres,as400=arwdRxt/postgres}
customers |17055 | 16398 | 10 | 0 | 16397 |
0 | 2831 | 80929 | 0 | 0 | t
| f | r |9 | 0 | 0 |
0 |0 | 0 | f | f | f |
f | {postgres=arwdRxt/postgres,rma_php=r/postgres}
(2 lignes)
bw_rma=# SELECT * FROM pg_catalog.pg_tables WHERE tablename =
'customers';
schemaname| tablename | tableowner | tablespace | hasindexes |
hasrules | hastriggers
-+---++++--+
-
bw_import_as400 | clients | postgres || f | f
| f
rma | clients | postgres || t | f
| f
(2 lignes)
Thanks a lot for your help :-)
Denis
Wojtek a écrit :
hi,
You may try checking:
SELECT * FROM pg_catalog.pg_class WHERE relname = 'customers'
SELECT * FROM pg_catalog.pg_tables WHERE tablename = 'customers'
to what's the status of your table.
Regards,
foo
Denis BUCHER wrote:
Hello,
Small correction to my previous email :
I have a strange problem since I moved some tables to a schema, some
tables are missing from the list (with \d or \dt) but they are still
present anyway ???!
Example :
$ psql mybase
Bienvenue dans psql 8.1.17, l'interface interactive de PostgreSQL.
Saisissez:
mybase=#
bw_rma=# \dt
Liste des relations
Schéma | Nom| Type | Propriétaire
-+--+---+--
import | rebates_products | table | postgres
import | rebates_customers| table | postgres
rma | categories | table | postgres
rma | customers| table | postgres
rma | defauts | table | postgres
rma | providers| table | postgres
No trace of my import.customers table ?
But if I do :
bw_rma=# SELECT count(*) FROM import.customers;
count
---
86703
(1 ligne)
My table is there and I can access it !!!
Any hint or help would be greatly appreciated !
I can do without it, but it's a little strange not to be able to list
the objects present in the database...
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Strange missing tables problem
Denis BUCHER wrote: Hello, It's even more strange : why, looks logical to me :) # \dt customers Liste des relations Schéma | Nom | Type | Propriétaire +-+---+-- rma| customers | table | postgres (1 ligne) you have 1 'customers' table in 'rma' schema and (my guess) currently you're connected as main user for this schema # \dt import.customers Liste des relations Schéma | Nom | Type | Propriétaire -+-+---+-- import | customers | table | postgres (1 ligne) you have 1 'customers' table in 'import' schema # \dt *.customers Liste des relations Schéma | Nom | Type | Propriétaire -+-+---+-- import | customers | table | postgres rma | customers | table | postgres (2 lignes) you have two tables 'customers', one in schema 'import', the other one in schema 'rma' I assume you want to keep two copies of this table, right? foo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Strange missing tables problem
Hello, Wojtek a écrit : It's even more strange : why, looks logical to me :) # \dt customers Liste des relations Schéma | Nom | Type | Propriétaire +-+---+-- rma| customers | table | postgres (1 ligne) you have 1 'customers' table in 'rma' schema and (my guess) currently you're connected as main user for this schema Yes but why the import.customers table doesn't appear, then ? # \dt import.customers Liste des relations Schéma | Nom | Type | Propriétaire -+-+---+-- import | customers | table | postgres (1 ligne) you have 1 'customers' table in 'import' schema # \dt *.customers Liste des relations Schéma | Nom | Type | Propriétaire -+-+---+-- import | customers | table | postgres rma | customers | table | postgres (2 lignes) you have two tables 'customers', one in schema 'import', the other one in schema 'rma' Yes that's correct I assume you want to keep two copies of this table, right? Yes, in fact they are two different tables... Denis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Strange missing tables problem
Hello,
# VACUUM pg_catalog.pg_class;
VACUUM
# VACUUM pg_catalog.pg_tables;
ATTENTION: ignore « pg_tables » --- could not execute VACUUM on indexes,
views or system tables
VACUUM
Denis
Bill Bartlett a écrit :
Possible xid rollover problem? (We saw behavior similar to this during a
recent
xid rollover fiasco, where tables didn't appear in the various catalogs and
psql
catalog commands, but the data was still there.)
What version of PostgreSQL are you on?
If you try a VACUUM on the pg_catalog.pg_class and pg_catalog.pg_tables
tables,
do the proper rows come back?
- Bill
-Original Message-
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
ow...@postgresql.org] On Behalf Of Denis BUCHER
Sent: Sunday, August 23, 2009 8:55 AM
To: Wojtek
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Strange missing tables problem
Hello,
That's what I found, do you see something inside that looks interesting ?
bw_rma=# SELECT * FROM pg_catalog.pg_class WHERE relname = 'customers';
relname | relnamespace | reltype | relowner | relam | relfilenode |
reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid |
relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers
| relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules
| relhassubclass | relacl
-+--+-+--+---+-+
---+--
+---+---+---+-+-+---
--+--
+---+-+--+--+-++
+-
++--
customers |17013 | 17022 | 10 | 0 | 17021 |
0 |16202 | 86685 | 0 | 0 | f
| f | r |9 | 0 | 0 |
0 |0 | 0 | f | f | f |
f | {postgres=arwdRxt/postgres,as400=arwdRxt/postgres}
customers |17055 | 16398 | 10 | 0 | 16397 |
0 | 2831 | 80929 | 0 | 0 | t
| f | r |9 | 0 | 0 |
0 |0 | 0 | f | f | f |
f | {postgres=arwdRxt/postgres,rma_php=r/postgres}
(2 lignes)
bw_rma=# SELECT * FROM pg_catalog.pg_tables WHERE tablename =
'customers';
schemaname| tablename | tableowner | tablespace | hasindexes |
hasrules | hastriggers
-+---++++--+
-
bw_import_as400 | clients | postgres || f | f
| f
rma | clients | postgres || t | f
| f
(2 lignes)
Thanks a lot for your help :-)
Denis
Wojtek a écrit :
hi,
You may try checking:
SELECT * FROM pg_catalog.pg_class WHERE relname = 'customers'
SELECT * FROM pg_catalog.pg_tables WHERE tablename = 'customers'
to what's the status of your table.
Regards,
foo
Denis BUCHER wrote:
Hello,
Small correction to my previous email :
I have a strange problem since I moved some tables to a schema, some
tables are missing from the list (with \d or \dt) but they are still
present anyway ???!
Example :
$ psql mybase
Bienvenue dans psql 8.1.17, l'interface interactive de PostgreSQL.
Saisissez:
mybase=#
bw_rma=# \dt
Liste des relations
Schéma | Nom| Type | Propriétaire
-+--+---+--
import | rebates_products | table | postgres
import | rebates_customers| table | postgres
rma | categories | table | postgres
rma | customers| table | postgres
rma | defauts | table | postgres
rma | providers| table | postgres
No trace of my import.customers table ?
But if I do :
bw_rma=# SELECT count(*) FROM import.customers;
count
---
86703
(1 ligne)
My table is there and I can access it !!!
Any hint or help would be greatly appreciated !
I can do without it, but it's a little strange not to be able to list
the objects present in the database...
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Denis Bucher
--
Denis Bucher Horus Digital Solutions sàrl Each problem has a solution
___
Tél. +41-22-8000625 Fax: +41-22-8000622 www.hsolutions.ch
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To
Re: [GENERAL] bytea corruption?
I'm probably a little late to this discussion, but I have had issues before
with BYTEA in postgres before as well, this is what I found worked.
use Digest::MD5;
use DBI qw(:sql_types);
use DBD::Pg qw(:pg_types);
sub InsertBin($$$)
{
my ( $dbh, $md5sum, $filename ) = @_;
open BIN,$filename || die Unable to open;
my $bin;
while (BIN){
$bin .= $_;
}
my $insertsql = qq( INSERT INTO enc_virus VALUES( ?,?,? )); # filename
-
md5sum - binary
my $sth = $dbh-prepare($insertsql);
$sth-bind_param(1,$filename);
$sth-bind_param(2,$md5sum);
$sth-bind_param(3,$bin, { pg_type = PG_BYTEA });
$sth-execute();
}
I hope this helps if you haven't figured it out yet
On Saturday 22 August 2009 03:48:25 pm Daniel Verite wrote:
Nathan Jahnke wrote:
thanks for your help. unfortunately i'm still getting corruption on
this particular data (available at
http://nate.quandra.org/data.bin.0.702601051229191 ) even with these
changes:
# ./bytea.pl
Argument DBD::Pg::PG_BYTEA isn't numeric in subroutine entry at
./bytea.pl line 18.
37652cf91fb8d5e41d3a90ea3a22ea61 != ce3fc63b88993af73fb360c70b7ec965
Ah, you also need to add
use DBD::Pg;
at the beginning of the script for DBD::Pg::PG_BYTEA to be properly
evaluated.
Best regards,
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Strange missing tables problem
Denis BUCHER dbuche...@hsolutions.ch writes: I really don't understand what's happening here ? \dt customers will show you the customers table that's visible according to your search_path setting. Apparently schema import is either not in your search path at all, or behind rma. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multiple table entries?
On Sun, Aug 23, 2009 at 2:18 PM, Jeff Rossjr...@wykids.org wrote:
I had to modify your query slightly to make it run--hope I got what you are
after!
select (h).* from (select
heap_page_items(get_raw_page('pg_namespace',0)) as h) as x;
http://www.openvistas.net/pageinspect.html
Incidentally, may as well ask the usual questions:
Did you upgrade this via pg_dump/pg_restore or via pg_migrator?
Are you running with fsync=off?
Are you running with synchronous_commit=off?
Have you had any system crashes?
Have you had any Postgres crashes?
Of the above only fsync=off combined with a system crash ought to be
unsafe, but it could be good to know which other events have occurred
to know which code paths have been exercised.
Do you still have pg_clog/ (that's kept longer than the xlog so I
would expect you would), how large is it? could you send that file? It
has a record of which transactions have committed or aborted, no
private data.
--
greg
http://mit.edu/~gsstark/resume.pdf
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multiple table entries?
Greg Stark wrote:
On Sun, Aug 23, 2009 at 2:18 PM, Jeff Rossjr...@wykids.org wrote:
I had to modify your query slightly to make it run--hope I got what you are
after!
select (h).* from (select
heap_page_items(get_raw_page('pg_namespace',0)) as h) as x;
http://www.openvistas.net/pageinspect.html
Incidentally, may as well ask the usual questions:
Did you upgrade this via pg_dump/pg_restore or via pg_migrator?
pg_dump
Are you running with fsync=off?
No.
Are you running with synchronous_commit=off?
No.
Have you had any system crashes?
No.
Have you had any Postgres crashes?
No.
Of the above only fsync=off combined with a system crash ought to be
unsafe, but it could be good to know which other events have occurred
to know which code paths have been exercised.
Do you still have pg_clog/ (that's kept longer than the xlog so I
would expect you would), how large is it? could you send that file? It
has a record of which transactions have committed or aborted, no
private data.
pg_clog is 32K. I've put it at http://www.openvistas.net/pg_clog
Jeff
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multiple table entries?
Greg Stark wrote: On Sun, Aug 23, 2009 at 5:02 PM, Greg Starkgsst...@mit.edu wrote: On Sun, Aug 23, 2009 at 2:18 PM, Jeff Rossjr...@wykids.org wrote: Incidentally, may as well ask the usual questions: And just for reference, what does pg_controldata print? _postgre...@heinlein:/var/postgresql $ pg_controldata pg_control version number:843 Catalog version number: 200904091 Database system identifier: 5368117850282091454 Database cluster state: in production pg_control last modified: Sun Aug 23 10:37:39 2009 Latest checkpoint location: 0/4203B43C Prior checkpoint location:0/4202F978 Latest checkpoint's REDO location:0/4203B43C Latest checkpoint's TimeLineID: 1 Latest checkpoint's NextXID: 0/116884 Latest checkpoint's NextOID: 866525 Latest checkpoint's NextMultiXactId: 1 Latest checkpoint's NextMultiOffset: 0 Time of latest checkpoint:Sun Aug 23 10:37:38 2009 Minimum recovery ending location: 0/0 Maximum data alignment: 4 Database block size: 8192 Blocks per segment of large relation: 131072 WAL block size: 8192 Bytes per WAL segment:16777216 Maximum length of identifiers:64 Maximum columns in an index: 32 Maximum size of a TOAST chunk:2000 Date/time type storage: 64-bit integers Float4 argument passing: by value Float8 argument passing: by reference -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multiple table entries?
On Sun, Aug 23, 2009 at 5:37 PM, Jeff Rossjr...@wykids.org wrote: pg_clog is 32K. I've put it at http://www.openvistas.net/pg_clog Sorry, I'm getting a 404 -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Figuring out relationships between tables.
Greetings, This is a general database design question. I've got a database where I need to hold information on employees. Every employee has much of the same information so I've created an Employee table. Now, some employees are supervisors or managers. An employee can only have 1 manager/supervisor but the hierarchy can be varying depths. For example... I have the following employees: Bob, Jill, Tom, Bill, Harry, Jane, Amy, Jim, Fred, Sue and June The management hierarchy works like this: Bob / |\ Jill Tom Bill / \ | \ Harry Jane Amy Jim || Fred Sue | June How can I create these relationships in the database? I don't want a Manager table that duplicates all the information for employees for the managers. Besides, a manager can have a manager who can have a manager... and so on. There is probably a simple solution to this problem but I'm not seeing it. Any help would be appreciated. Thanks, Andrew -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Strange missing tables problem
Le dimanche 23 août 2009 à 14:26:06, Denis BUCHER a écrit : Hello, I have a strange problem since I moved some tables to a schema, some tables are missing from the list (with \d or \dt) but they are still present anyway ???! Example : $ psql mybase Bienvenue dans psql 8.1.17, l'interface interactive de PostgreSQL. Saisissez: mybase=# bw_rma=# \dt Liste des relations Schéma | Nom| Type | Propriétaire -+--+---+-- import | rebates_products | table | postgres import | rebates_customers| table | postgres rma | categories | table | postgres rma | customers| table | postgres rma | defauts | table | postgres rma | providers| table | postgres No trace of my import.clients table ? But if I do : bw_rma=# SELECT count(*) FROM import.customers; count --- 86703 (1 ligne) My table is there and I can access it !!! Any hint or help would be greatly appreciated ! I can do without it but, it's a little strange not to be able to list the objects present in the database... \d does not show all the objects available in the database. If one is available in schema A and in schema B, it will be displayed at most once, depending on your search_path configuration. I suppose you have something like 'rma, import, ...' for search_path, so it only displays rma.customers and not impor.customers. Regards. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Figuring out relationships between tables.
On Aug 23, 2009, at 7:47 AM, Andrew Cooper wrote: An employee can only have 1 manager/supervisor but the hierarchy can be varying depths. Traditionally, that's done by having a supervisor field as part of the employee record, with either NULL or a special marker value to indicate no supervisor. The new WITH RECURSIVE functionality of 8.4 makes dealing with tree structures like this much easier. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multiple table entries?
On Sun, Aug 23, 2009 at 6:23 PM, Greg Starkgsst...@mit.edu wrote:
On Sun, Aug 23, 2009 at 5:37 PM, Jeff Rossjr...@wykids.org wrote:
pg_clog is 32K. I've put it at http://www.openvistas.net/pg_clog
Sorry, I'm getting a 404
For what it's worth this is what the heap dump shows. I don't
understand why you're seeing these tuples show up if they're all
properly marked as updated and XMAX_COMMITTED.
The last tuple is marked strangely I think. I don't think it's
supposed to have XMAX_INVALID if xmax is 0 but I could be wrong. Also,
I don't understand why it's marked as UPDATED if ctid and xmax aren't
set.
Could you also send
select (h).* from (select page_header(get_raw_page('pg_namespace',0))
as h) as x;
I'm wondering if the page allvisible flag is set. The visibility map
is one of the few 8.4 patches which impact transaction visibility.
lp lp_off flags lp_len t_xmin t_xmax field3 t_ctid t_info2 t_info
3 76161 141 2 897 0 (0,6) 16387 9482
HOT_UPDATED, UPDATED, XMAX_COMMITTED, XMIN_COMMITTED
6 73401 129 897 898 0 (0,7) -16381 9482
HOT_UPDATED, HEAP_ONLY_TUPLE, UPDATED, XMAX_COMMITTED, XMIN_COMMITTED
7 72201 117 898 899 0 (0,9) -16381 9482
HOT_UPDATED, HEAP_ONLY_TUPLE, UPDATED, XMAX_COMMITTED, XMIN_COMMITTED
9 70881 129 899 900 0 (0,10) -16381 9482
HOT_UPDATED, HEAP_ONLY_TUPLE, UPDATED, XMAX_COMMITTED, XMIN_COMMITTED
10 69441 141 900 0 0 (0,10) -32765 10506
UPDATED, XMAX_INVALID, XMIN_COMMITTED
--
greg
http://mit.edu/~gsstark/resume.pdf
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multiple table entries?
Greg Stark gsst...@mit.edu writes: The last tuple is marked strangely I think. I don't think it's supposed to have XMAX_INVALID if xmax is 0 but I could be wrong. Also, I don't understand why it's marked as UPDATED if ctid and xmax aren't set. No, that all looks right to me. UPDATE sets HEAP_UPDATED on the newer version of the row, not the older one. What looks interesting to me is that the last update isn't marked HEAP_ONLY_TUPLE, ie, it's not in the same HOT chain. Why is that I wonder ... I'm wondering if the page allvisible flag is set. The visibility map is one of the few 8.4 patches which impact transaction visibility. But we don't use that while examining individual tuples, do we? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Figuring out relationships between tables.
On Sun, Aug 23, 2009 at 09:47:06AM -0500, Andrew Cooper wrote:
Greetings,
This is a general database design question. I've got a database where I
need to hold information on employees. Every employee has much of the
same information so I've created an Employee table. Now, some employees
are supervisors or managers. An employee can only have 1
manager/supervisor but the hierarchy can be varying depths. For
example...
I have the following employees:
Bob, Jill, Tom, Bill, Harry, Jane, Amy, Jim, Fred, Sue and June
The management hierarchy works like this:
Bob
/ |\
Jill Tom Bill
/ \ | \
Harry Jane Amy Jim
||
Fred Sue
|
June
How can I create these relationships in the database? I don't want a
Manager table that duplicates all the information for employees for the
managers. Besides, a manager can have a manager who can have a
manager... and so on. There is probably a simple solution to this
problem but I'm not seeing it. Any help would be appreciated.
Thanks,
Andrew
Before 8.4, you had to do some awful hacks. With 8.4, you use Common
Table Expressions
http://www.postgresql.org/docs/current/static/queries-with.html to
do something like this:
CREATE TABLE employee (
employee_id INTEGER PRIMARY KEY, /* You'll an actual uniqueness
constraint besides this, but
that's for another time. */
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
);
CREATE TABLE organization (
employee_id INTEGER NOT NULL
REFERENCES employee(employee_id),
boss_id INTEGER /* The root of this tree has no boss. */
REFERENCES employee(employee_id),
CHECK(employee_id boss_id)
);
Now you have your table of organization separate from the employee
table. There are several ways to ensure that this is in fact a tree,
but let's assume you're handling this manually.
Now, when you want an org chart, you can do:
WITH RECURSIVE t AS (
SELECT e.employee_id, e.first, e.last, ARRAY[e.employee_id] AS path
FROM employee AS e
JOIN organization AS o ON (
e.employee_id = o.employee_id AND
o.boss_id IS NULL
)
UNION ALL
SELECT e.employee_id, e.first, e.last, t.path || e.employee_id AS path
FROM employee AS e
JOIN t ON (
e.boss_id = t.employee_id AND
e.employee_id ANY(t.path) /* Prevent loops */
)
)
SELECT
REPEAT('--', array_upper(t.path)-1) || employee_id as employee_id,
t.first || ' ' || t.last AS Name
FROM t;
Cheers,
David.
--
David Fetter da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] bytea corruption?
thank you very much, all. i was able to insert my data and get it back
out with a matching hash. my problems were caused by confusion going
between plperlu - which has the bytea storage explicit custom encoding
requirement - and regular perl using dbd::pg - which does not as long
as the data type is specified.
so, for reference:
plperlu:
explicitly encode bytea before insert via spi: yes
explicitly decode bytea after select via spi: yes
perl (dbd::pg):
explicitly encode bytea before insert via sth: no if using { pg_type
= DBD::Pg::PG_BYTEA } as third arg in bind_param() (thanks daniel
verite)
explicitly decode bytea after select via sth: no
nathan
On Sun, Aug 23, 2009 at 9:20 AM, Colin
Streicherco...@obviouslymalicious.com wrote:
I'm probably a little late to this discussion, but I have had issues before
with BYTEA in postgres before as well, this is what I found worked.
use Digest::MD5;
use DBI qw(:sql_types);
use DBD::Pg qw(:pg_types);
sub InsertBin($$$)
{
my ( $dbh, $md5sum, $filename ) = @_;
open BIN,$filename || die Unable to open;
my $bin;
while (BIN){
$bin .= $_;
}
my $insertsql = qq( INSERT INTO enc_virus VALUES( ?,?,? )); #
filename -
md5sum - binary
my $sth = $dbh-prepare($insertsql);
$sth-bind_param(1,$filename);
$sth-bind_param(2,$md5sum);
$sth-bind_param(3,$bin, { pg_type = PG_BYTEA });
$sth-execute();
}
I hope this helps if you haven't figured it out yet
On Saturday 22 August 2009 03:48:25 pm Daniel Verite wrote:
Nathan Jahnke wrote:
thanks for your help. unfortunately i'm still getting corruption on
this particular data (available at
http://nate.quandra.org/data.bin.0.702601051229191 ) even with these
changes:
# ./bytea.pl
Argument DBD::Pg::PG_BYTEA isn't numeric in subroutine entry at
./bytea.pl line 18.
37652cf91fb8d5e41d3a90ea3a22ea61 != ce3fc63b88993af73fb360c70b7ec965
Ah, you also need to add
use DBD::Pg;
at the beginning of the script for DBD::Pg::PG_BYTEA to be properly
evaluated.
Best regards,
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multiple table entries?
On Sun, Aug 23, 2009 at 7:00 PM, Tom Lanet...@sss.pgh.pa.us wrote: Greg Stark gsst...@mit.edu writes: The last tuple is marked strangely I think. I don't think it's supposed to have XMAX_INVALID if xmax is 0 but I could be wrong. Also, I don't understand why it's marked as UPDATED if ctid and xmax aren't set. No, that all looks right to me. UPDATE sets HEAP_UPDATED on the newer version of the row, not the older one. What looks interesting to me is that the last update isn't marked HEAP_ONLY_TUPLE, ie, it's not in the same HOT chain. Why is that I wonder ... I'm wondering if the page allvisible flag is set. The visibility map is one of the few 8.4 patches which impact transaction visibility. But we don't use that while examining individual tuples, do we? We don't use the visibility map itself but we *do* use the page header's all visible bit. On a sequential scan we skip the visibility check for tuples on the page if the page header bit is set. -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multiple table entries?
Greg Stark gsst...@mit.edu writes: On Sun, Aug 23, 2009 at 7:00 PM, Tom Lanet...@sss.pgh.pa.us wrote: But we don't use that while examining individual tuples, do we? We don't use the visibility map itself but we *do* use the page header's all visible bit. On a sequential scan we skip the visibility check for tuples on the page if the page header bit is set. Urgh. I bet that's where the problem is then. Some path is failing to clear that bit, or maybe there's a race condition that allows it to become set incorrectly (ie right after somebody else adds a tuple). regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multiple table entries?
On Sun, Aug 23, 2009 at 7:34 PM, Tom Lanet...@sss.pgh.pa.us wrote: Greg Stark gsst...@mit.edu writes: On Sun, Aug 23, 2009 at 7:00 PM, Tom Lanet...@sss.pgh.pa.us wrote: But we don't use that while examining individual tuples, do we? We don't use the visibility map itself but we *do* use the page header's all visible bit. On a sequential scan we skip the visibility check for tuples on the page if the page header bit is set. Urgh. I bet that's where the problem is then. Some path is failing to clear that bit, or maybe there's a race condition that allows it to become set incorrectly (ie right after somebody else adds a tuple). Well it only gets set in vacuum when we have the vacuum cleanout lock so I can't see it getting set incorrectly unless there's a simple logic error in vacuum. I just scanned it and it looks right to me. I can imagine it failing to be unset. Or being unset but allowing a race condition window before it's unset when it's wrong. That doesn't explain why it the HEAP_ONLY_TUPLE flag would be missing though. Also, vacuum logs a warning if the flag is found to be set and shouldn't be. I suppose that could easily be missed though if it's autovacuum that ran it. Also, the flag might fail to be cleared but then be cleared later by another operation. I wonder how much damage it can do while it's wrong. -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multiple table entries?
On Sun, Aug 23, 2009 at 5:02 PM, Greg Starkgsst...@mit.edu wrote: On Sun, Aug 23, 2009 at 2:18 PM, Jeff Rossjr...@wykids.org wrote: Incidentally, may as well ask the usual questions: And just for reference, what does pg_controldata print? -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] libpq performance
Hi, I have a sql that only takes 0.3 ms to run when using psql with explain analyze. However, when I execute it using PQexec, it takes 12ms for PGexec. Does anyone know why it is that slow? My db server is in the internal network, so there should not be any latency issue. Any suggestion will be greatly appreciated. Thanks, JB
Re: [GENERAL] Multiple table entries?
Greg Stark gsst...@mit.edu writes: On Sun, Aug 23, 2009 at 7:34 PM, Tom Lanet...@sss.pgh.pa.us wrote: Urgh. I bet that's where the problem is then. Some path is failing to clear that bit, or maybe there's a race condition that allows it to become set incorrectly (ie right after somebody else adds a tuple). Well it only gets set in vacuum when we have the vacuum cleanout lock so I can't see it getting set incorrectly unless there's a simple logic error in vacuum. I just scanned it and it looks right to me. heap_update is broken. Details left as an exercise for the reader (hint: Heikki has blatantly ignored the WAL-logging rules in transam/README) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multiple table entries?
Greg Stark wrote: On Sun, Aug 23, 2009 at 5:37 PM, Jeff Rossjr...@wykids.org wrote: pg_clog is 32K. �I've put it at http://www.openvistas.net/pg_clog Sorry, I'm getting a 404 Oops--fixed now. Jeff -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] What approach should I use instead of creating tables on the fly?
Hello! I'm looking for a way to store quite a bit large amount of data in an efficient manner. There is about 8GB per day to store and I feel that approach I'm using now isn't what I want to end up with. Let me describe how I've tried to solve this problem before I ask my question in more detail. Data to be inserted consists of user requests to several sites coming with a few additional fields (like user id). At first I decided to store all the requests coming to my server in one table called request. The db structure was as follows: CREATE TABE site ( site_id varchar(80) PRIMARY KEY, -- skipped ) CREATE TABLE request ( user_id int PRIMARY KEY, site_id varchar(80) REFERENCES site, -- skipped ) Actually it wasn't the really good idea to store millions of requests in only one table, because the index of request table doesn't seem to work fast enough on so much data. Now I'm using separate table for each site to store requests. Those tables are created dynamically by the client application when command create_site is called. Unfortunatelly there is one disadvantage coming with this solution which I don't like: features aimed to control reference integrity (like REFERENCES constraint) are no longer my friends. Is there a way that mixes the advantages (reference integrity + good performance) of those ones I listed above? I started learning PostgreSQL/SQL some weeks ago, so there may be obvious solution I haven't ever hear about. Please point out it. Thanks. -- Sergey Samokhin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multiple table entries?
Tom Lane wrote: Greg Stark gsst...@mit.edu writes: On Sun, Aug 23, 2009 at 7:34 PM, Tom Lanet...@sss.pgh.pa.us wrote: Urgh. I bet that's where the problem is then. Some path is failing to clear that bit, or maybe there's a race condition that allows it to become set incorrectly (ie right after somebody else adds a tuple). Well it only gets set in vacuum when we have the vacuum cleanout lock so I can't see it getting set incorrectly unless there's a simple logic error in vacuum. I just scanned it and it looks right to me. heap_update is broken. Details left as an exercise for the reader (hint: Heikki has blatantly ignored the WAL-logging rules in transam/README) regards, tom lane Well, as the reader that started this all ;-) should I be worried? I don't see any obvious problem with the way 8.4.0 is working. Nearly every web page on my server is stored in postgres, and we're still churning out pages, no problem. Should I do a pg_dump and reinstall? Roll back to 8.3.7? Or just relax, don't worry and have a sparkling adult beverage? Thanks, Jeff (proceeding with relaxing, not worrying, and so on) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Figuring out relationships between tables.
Thanks, guys. This really helps out. Andrew -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multiple table entries?
On Sun, Aug 23, 2009 at 9:06 PM, Jeff Rossjr...@wykids.org wrote:
pglogd=# select (h).* from (select
page_header(get_raw_page('pg_namespace',0))
pglogd(# as h) as x;
lsn | tli | flags | lower | upper | special | pagesize | version |
prune_xid
---+-+---+---+---+-+--+-+---
0/1A15904 | 1 | 5 | 64 | 6944 | 8192 | 8192 | 4 |
897
Well this confirms that the bug involves the all-visible flag as the 5
is PD_ALL_VISIBLE + PD_HAS_FREE_LINES
It's been overtaken by events as Tom has spotted a problem already.
--
greg
http://mit.edu/~gsstark/resume.pdf
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multiple table entries?
Jeff Ross jr...@wykids.org writes: Tom Lane wrote: heap_update is broken. Details left as an exercise for the reader Well, as the reader that started this all ;-) should I be worried? Should I do a pg_dump and reinstall? Roll back to 8.3.7? Or just relax, don't worry and have a sparkling adult beverage? Well, it's a pretty bad bug but as far as I can see a simple VACUUM table command should fix it up --- would you confirm? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multiple table entries?
Tom Lane wrote: Jeff Ross jr...@wykids.org writes: Tom Lane wrote: heap_update is broken. Details left as an exercise for the reader Well, as the reader that started this all ;-) should I be worried? Should I do a pg_dump and reinstall? Roll back to 8.3.7? Or just relax, don't worry and have a sparkling adult beverage? Well, it's a pretty bad bug but as far as I can see a simple VACUUM table command should fix it up --- would you confirm? regards, tom lane Hah! It did indeed clear it up! pglogd=# \d List of relations Schema | Name | Type |Owner +--+---+- public | full_entries | table | _postgresql public | full_temp| table | jross public | log_entries | table | _postgresql public | page_hits| table | _postgresql public | total_hits | table | _postgresql (5 rows) Thanks, Tom and Greg. Looking forward to 8.4.1 :-) Jeff -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multiple table entries?
Jeff Ross jr...@wykids.org writes: Tom Lane wrote: Well, it's a pretty bad bug but as far as I can see a simple VACUUM table command should fix it up --- would you confirm? Hah! It did indeed clear it up! [ thinks... ] Actually, that only proves that the PD_ALL_VISIBLE fixup logic in vacuumlazy.c does what it's supposed to do; it doesn't in itself show anything about how the flag got to be wrong. The path that I'm seeing is enabled by the bogus coding that messes with the flag after having done XLogInsert --- that's forbidden by our coding rules, and the reason is that if XLogInsert chooses to log the entire page then the replay routine will assume that all the required changes got applied already. It's fairly easy to demonstrate the problem: create table foo (f1 int, f2 int); insert into foo values (1,2); insert into foo values (3,4); select ctid,xmin,xmax,* from foo; vacuum foo; vacuum foo; checkpoint; update foo set f2 = f2 + 1; select ctid,xmin,xmax,* from foo; -- now cause a crash, eg kill -9 on this backend After recovery, there'll be two visible copies of the two rows. However, this theory requires that you had a backend crash, and you averred upthread that you hadn't ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] libpq performance
On Mon, 2009-08-24 at 00:08 +0800, Juan Backson wrote: I have a sql that only takes 0.3 ms to run when using psql with explain analyze. explain analyze reports server-side execution time. However, when I execute it using PQexec, it takes 12ms for PGexec. Does anyone know why it is that slow? ... and if you're timing PQexec you're probably taking the client-side time, ie the time from start of PQexec call to the time the PQexec call returns. That means that network latency *is* a factor, albeit a small one. I don't think EXPLAIN ANALYZE will report any delays due to lock acquisition or anything like that either. However, most of the difference probably comes from the time taken to parse and plan the statement. It'd help if you actually provided the EXPLAIN ANALYZE output and the statement in question so there was less guesswork involved. My db server is in the internal network, so there should not be any latency issue. Rather than assuming that, I'd recommend measuring it: - Run the test program on the DB server with a connection over the loopback interface (127.0.0.1); and - if the DB server is UNIX based, run the test program on the DB server with a connection over a UNIX socket; and - Use Wireshark to examine the actual network traffic to see how big a gap there is between request and response However, as I said above I personally expect the difference is mostly in parsing and planning time. There are ways to reduce planning time (at the cost of potentially inferior query plans) - but if you're really that worried about query execution time, might you perhaps be executing a huge number of tiny queries in a situation where one or two bigger queries can get the job done more quickly? -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What approach should I use instead of creating tables on the fly?
On Mon, 2009-08-24 at 00:31 +0400, Sergey Samokhin wrote: Unfortunatelly there is one disadvantage coming with this solution which I don't like: features aimed to control reference integrity (like REFERENCES constraint) are no longer my friends. Is there a way that mixes the advantages (reference integrity + good performance) of those ones I listed above? Table partitioning and table inheritance. See the manual and the list archives. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Could not open relation XXX: No such file or directory
Alan Millington wrote: However, on looking at the matter again, I am now almost certain that I caused the problem myself. I have a Python function which (as a workaround to a problem which exists in Python 2.4, the version to which Postgres 8.1.4 is tied) executes a chdir. It appears that once this has happened, the current Postgres session is no longer able to find any new data files, though evidently it is still able to use those that it has located previously. If you can confirm that Postgres does indeed rely on the current working directory to locate its data files, the problem is solved. Yes, Postgres expects that the CWD is not changed. Files that were previously opened continue to work fine, because they are kept open. Do not chdir() in a function. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting listed on Community Guide to PostgreSQL GUI Tools
Thomas Kellerer wrote: Hi, I was going through the list of application at http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools and was wondering whom I should contact to get my application listed there as well. It is a Java based SQL tool (http://www.sql-workbench.net) and supports PostgreSQL (as a matter of fact I do most of the DBMS independent development agains my local PG database). I have also seen that some of the listed applications don't seem to be active any longer (PGAccess, Xpg, pginhaler). Wouldn't it make sense to clean up a bit there as well? I think that page is pretty much unmaintained. Feel free to add your product, provided you don't turn it into a marketing opportunity (and be prepared for others to edit your description). As far as dead software, I think you should create a section at the end of the page and move dead projects there. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how to return field based on field= NULL or not
Hi, In my table, I have fieldA and fieldB. At any point in time, only one of these fields can have data in it. The other is NULL. Instead of select fieldA, fieldB from table, I want it to return either fieldA or fieldB depends on whether it is NULL or not. The reason is because I want to use select array_to_string(array_accum(field A or field B) ,',') from table. Is it possible to do it that way? Thanks, JB
Re: [GENERAL] how to return field based on field= NULL or not
Juan Backson wrote: Hi, In my table, I have fieldA and fieldB. At any point in time, only one of these fields can have data in it. The other is NULL. Instead of select fieldA, fieldB from table, I want it to return either fieldA or fieldB depends on whether it is NULL or not. The reason is because I want to use select array_to_string(array_accum(field A or field B) ,',') from table. Is it possible to do it that way? Thanks, JB The two main ways of doing this are COALESCE(fieldA, fieldB) http://www.postgresql.org/docs/8.3/interactive/functions-conditional.html#AEN14484 and CASE WHEN fieldA IS NULL THEN fieldB ELSE fieldA END; http://www.postgresql.org/docs/8.3/interactive/functions-conditional.html#AEN14434 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to return field based on field= NULL or not
Hi, I assume one field will always be NULL and one will always has data in it. select coalesce(fieldA, fieldB) from table Regards, foo Juan Backson wrote: Hi, In my table, I have fieldA and fieldB. At any point in time, only one of these fields can have data in it. The other is NULL. Instead of select fieldA, fieldB from table, I want it to return either fieldA or fieldB depends on whether it is NULL or not. The reason is because I want to use select array_to_string(array_accum(field A or field B) ,',') from table. Is it possible to do it that way? Thanks, JB -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Install from Source On Windows - University of Sydney Research
Hello, I have downloaded and built the 8.2.4 postgreSQL from source. This was done by running the build.bat file under src\tools\msvc. I do get a few errors with some contrib projects, but I do not care about them. When i do install(install.pl), there is a destination directory created. Now my question is how do i run postgreSQL from within here. Is there a command line script etc. for this ? Or how can i connect it to pgADMin i.e. the new exe as well as bki file. Thanks Kushal
Re: [GENERAL] how to return field based on field= NULL or not
On Sun, Aug 23, 2009 at 11:43 PM, Juan Backsonjuanback...@gmail.com wrote: Hi, Thank you for your help. What I want to dos is as follows: SELECT COALESCE(fieldA::text,fieldB||fieldC||fieldD) from ring where group_id = 1 if fieldB is NULL, i will want it to return fieldC|| fieldD if fieldB and fieldC is null, I want it to return fieldD. Basically, fieldD is always going to have data, but fieldB and fieldC can be NULL. How can I revise the query to meet that purpose? Just coalesce them all to '' and cat them: ... coalesce(a,'')||coalesce(b,'')||coalesce(c,'')||d ... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
