Re: [GENERAL] cannot delete some records [9.3]
On Thu, 5 Dec 2013, Andy Colson wrote:
On 12/5/2013 4:05 PM, Frank Miles wrote:
The table schema is {\d
credmisc}:
And this is all owned by: {\dp credmisc}
You have a table credmisc, in schema credmisc, owned by credmisc?
It could be a path problem. Maybe trigger should be:
Sorry for the perhaps overly compact way that I was describing
how I recovered the schema (by executing \d credmisc) and ownership
(\dp credmisc). It's owned by 'fpm'.
trig_credmisc_updt BEFORE UPDATE ON credmisc.credmisc FOR EACH ROW EXECUTE
PROCEDURE credmisc.trigonupdtcredmisc()
trig_credmisc_ins BEFORE INSERT ON credmisc FOR EACH ROW EXECUTE
PROCEDURE trigoninscredmisc()
trig_credmisc_updt BEFORE UPDATE ON credmisc FOR EACH ROW EXECUTE
PROCEDURE trigonupdtcredmisc()
Access privileges
Schema | Name | Type | Access privileges | Column access
privileges
+--+---+---+--
public | credmisc | table | fpm=ardxt/fpm+|
| | | bioeng=r/fpm |
Could we see the permissions on the functions too?
-Andy
As a trigger, can it be 'owned'? And since the problem occurs even
when the trigger is dropped, it seems ultimately not involved.
Thanks for trying, though!
-Frank
--
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] cannot delete some records [9.3]
On Thu, 5 Dec 2013, Andy Colson wrote:
On 12/5/2013 4:05 PM, Frank Miles wrote:
[snip]
Table "public.credmisc"
Column | Type |Modifiers
--+--+--
cm_id| integer | not null default
nextval('credmisc_cm_id_seq'::regclass)
crtype | character(1) | not null
ref_id | integer | not null
raw_amt | double precision | not null
resolved | boolean | not null default false
dtwhen | date | not null default ('now'::text)::date
explan | text | not null
Indexes:
"credmisc_pkey" PRIMARY KEY, btree (cm_id)
Check constraints:
"credmisc_crtype_check" CHECK (crtype = 'b'::bpchar OR crtype =
'p'::bpchar)
"credmisc_raw_amt_check" CHECK (raw_amt >= 0.02::double precision)
Referenced by:
TABLE "credtrans" CONSTRAINT "credtrans_cm_id_fkey" FOREIGN KEY
(cm_id) REFERENCES credmisc(cm_id)
Triggers:
trig_credmisc_ins BEFORE INSERT ON credmisc FOR EACH ROW EXECUTE
PROCEDURE trigoninscredmisc()
trig_credmisc_updt BEFORE UPDATE ON credmisc FOR EACH ROW EXECUTE
PROCEDURE trigonupdtcredmisc()
And this is all owned by: {\dp credmisc}
Access privileges
Schema | Name | Type | Access privileges | Column access
privileges
+--+---+---+--
public | credmisc | table | fpm=ardxt/fpm+|
| | | bioeng=r/fpm |
Yet when I try to delete some records:
delete from credmisc where cm_id < -100 and ref_id < 0;
what I get back is:
ERROR: permission denied for relation credmisc
CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."credmisc" x
WHERE "cm_id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"
Neither dropping the trigger nor performing the
'delete'
operation as user 'postgres' changes anything. There's nothing
different in
the logs. It works perfectly fine in 8.4. And most of the other dozens of
tables don't have this problem. Selecting the data looks fine.
Anyone have a clue as to what I'm missing? TIA!
-Frank
{p.s. yes, cm_id won't normally be negative... some negative values
were inserted as part of the unit testing, which avoids confusion
with existing positive value. That shouldn't be a problem, right?}
When you drop trig_credmisc_updt, you still get the error like:
ERROR: permission denied for relation credmisc
CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."credmisc" x
WHERE "cm_id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"
I assume that select statement is comming from function trigonupdtcredmisc(),
right?
-Andy
I can't see how - there's nothing in the trigger like that, and I still
get the same message even when the trigger is dropped.
--
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] cannot delete some records [9.3]
On 12/5/2013 4:05 PM, Frank Miles wrote:
The table schema is {\d
credmisc}:
And this is all owned by: {\dp credmisc}
You have a table credmisc, in schema credmisc, owned by credmisc?
It could be a path problem. Maybe trigger should be:
trig_credmisc_updt BEFORE UPDATE ON credmisc.credmisc FOR EACH ROW
EXECUTE PROCEDURE credmisc.trigonupdtcredmisc()
trig_credmisc_ins BEFORE INSERT ON credmisc FOR EACH ROW EXECUTE
PROCEDURE trigoninscredmisc()
trig_credmisc_updt BEFORE UPDATE ON credmisc FOR EACH ROW EXECUTE
PROCEDURE trigonupdtcredmisc()
Access privileges
Schema | Name | Type | Access privileges | Column access
privileges
+--+---+---+--
public | credmisc | table | fpm=ardxt/fpm+|
| | | bioeng=r/fpm |
Could we see the permissions on the functions too?
-Andy
--
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] cannot delete some records [9.3]
On 12/5/2013 4:05 PM, Frank Miles wrote:
I'm in the process of moving from a server running postgresql-8.4
(Debian-oldstable)
to a newer machine running postgresql-9.3. The dumpall-restore process
seemed to
go perfectly. In running my self-test script, I discovered that one of
the tables
couldn't be cleared of some unit-test entries. The table schema is {\d
credmisc}:
Table "public.credmisc"
Column | Type |Modifiers
--+--+--
cm_id| integer | not null default
nextval('credmisc_cm_id_seq'::regclass)
crtype | character(1) | not null
ref_id | integer | not null
raw_amt | double precision | not null
resolved | boolean | not null default false
dtwhen | date | not null default ('now'::text)::date
explan | text | not null
Indexes:
"credmisc_pkey" PRIMARY KEY, btree (cm_id)
Check constraints:
"credmisc_crtype_check" CHECK (crtype = 'b'::bpchar OR crtype =
'p'::bpchar)
"credmisc_raw_amt_check" CHECK (raw_amt >= 0.02::double precision)
Referenced by:
TABLE "credtrans" CONSTRAINT "credtrans_cm_id_fkey" FOREIGN KEY
(cm_id) REFERENCES credmisc(cm_id)
Triggers:
trig_credmisc_ins BEFORE INSERT ON credmisc FOR EACH ROW EXECUTE
PROCEDURE trigoninscredmisc()
trig_credmisc_updt BEFORE UPDATE ON credmisc FOR EACH ROW EXECUTE
PROCEDURE trigonupdtcredmisc()
And this is all owned by: {\dp credmisc}
Access privileges
Schema | Name | Type | Access privileges | Column access
privileges
+--+---+---+--
public | credmisc | table | fpm=ardxt/fpm+|
| | | bioeng=r/fpm |
Yet when I try to delete some records:
delete from credmisc where cm_id < -100 and ref_id < 0;
what I get back is:
ERROR: permission denied for relation credmisc
CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."credmisc" x
WHERE "cm_id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"
Neither dropping the trigger nor performing the
'delete'
operation as user 'postgres' changes anything. There's nothing
different in
the logs. It works perfectly fine in 8.4. And most of the other dozens of
tables don't have this problem. Selecting the data looks fine.
Anyone have a clue as to what I'm missing? TIA!
-Frank
{p.s. yes, cm_id won't normally be negative... some negative values
were inserted as part of the unit testing, which avoids confusion
with existing positive value. That shouldn't be a problem, right?}
When you drop trig_credmisc_updt, you still get the error like:
> ERROR: permission denied for relation credmisc
> CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."credmisc" x
> WHERE "cm_id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"
I assume that select statement is comming from function
trigonupdtcredmisc(), right?
-Andy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] cannot delete some records [9.3]
I'm in the process of moving from a server running postgresql-8.4
(Debian-oldstable)
to a newer machine running postgresql-9.3. The dumpall-restore process seemed
to
go perfectly. In running my self-test script, I discovered that one of the
tables
couldn't be cleared of some unit-test entries. The table schema is {\d
credmisc}:
Table "public.credmisc"
Column | Type |Modifiers
--+--+--
cm_id| integer | not null default
nextval('credmisc_cm_id_seq'::regclass)
crtype | character(1) | not null
ref_id | integer | not null
raw_amt | double precision | not null
resolved | boolean | not null default false
dtwhen | date | not null default ('now'::text)::date
explan | text | not null
Indexes:
"credmisc_pkey" PRIMARY KEY, btree (cm_id)
Check constraints:
"credmisc_crtype_check" CHECK (crtype = 'b'::bpchar OR crtype = 'p'::bpchar)
"credmisc_raw_amt_check" CHECK (raw_amt >= 0.02::double precision)
Referenced by:
TABLE "credtrans" CONSTRAINT "credtrans_cm_id_fkey" FOREIGN KEY (cm_id)
REFERENCES credmisc(cm_id)
Triggers:
trig_credmisc_ins BEFORE INSERT ON credmisc FOR EACH ROW EXECUTE PROCEDURE
trigoninscredmisc()
trig_credmisc_updt BEFORE UPDATE ON credmisc FOR EACH ROW EXECUTE PROCEDURE
trigonupdtcredmisc()
And this is all owned by: {\dp credmisc}
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
+--+---+---+--
public | credmisc | table | fpm=ardxt/fpm+|
| | | bioeng=r/fpm |
Yet when I try to delete some records:
delete from credmisc where cm_id < -100 and ref_id < 0;
what I get back is:
ERROR: permission denied for relation credmisc
CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."credmisc" x WHERE
"cm_id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"
Neither dropping the trigger nor performing the 'delete'
operation as user 'postgres' changes anything. There's nothing different in
the logs. It works perfectly fine in 8.4. And most of the other dozens of
tables don't have this problem. Selecting the data looks fine.
Anyone have a clue as to what I'm missing? TIA!
-Frank
{p.s. yes, cm_id won't normally be negative... some negative values
were inserted as part of the unit testing, which avoids confusion
with existing positive value. That shouldn't be a problem, right?}
--
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] Similarity search for sentences
Janek Sendrowski wrote: > I already had a try with gist/gin-index-based trigramm search > (pg_trgm extension), fulltextsearch (tsearch2 extension) and a > pivot-based indexing (Fixed Query Array), but it's all to slow or > not suitable. When you tried tsearch2, did you use a trigger to store the tsvector, or did you use a functional index? I found the former to be a couple orders of magnitude faster with an index on court document text. Likewise, the trigram searches I used in production had wildly different performance depending on the similarity threshold or the LIMIT for KNN searches. You may want to get the most promising technology to run as fast as you can, and then post to the pgsql-performance list with the information suggested here: http://wiki.postgresql.org/wiki/SlowQueryQuestions The problems may be solvable, but nobody is likely to know what to suggest without more to work with. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Fwd: Help！Why CPU Usage and LoadAverage Jump up Suddenly
On 12/5/2013 12:46 AM, 吕晓旭 wrote: We find so weird problem on our productive PostgreSQL system. And I don't know how could I do to resolve this problem. We deployed PostgreSQL 9.2.4 on two system environments, and the performances between them are absolutely different. one of them it's perfect, and the other one lets me down, CPU Usage and LoadAverage Jumped up Suddenly when concurrency smoothly rising up, simultaneously, average response time become unacceptable. I'm curious why you built your own postgres instead of using the yum.postgresql.com repository versions? and I second the suggestion, IO performance is likely a major factor here. also, you don't give your postgresql.conf tuning settings, file systems configurations, hardware storage configurations, etc. -- john r pierce 37N 122W somewhere on the middle of the left coast -- 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] vacuum_freeze_table_age for 9.3.2
On 12/5/2013 11:29 AM, Giuseppe Broccolo wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Il 05/12/2013 17:16, Andy Colson ha scritto: The docs say vacuum, but the param is vacuum_freeze_table_age, so do I need to "vacuum freeze" all the tables, or is vacuum enough? Also, will "set vacuum_freeze_table_age = 0; vacuum freeze;" work, or do I need to modify the postgresql.conf and reload? Setting vacuum_freeze_table_age to 0 forces VACUUM to always scan all pages, effectively ignoring the visibility map. In this way a scan of the whole table is done, ensuring all old XIDs are replaced by FrozenXID. vacuum_freeze_table_age is a parameter with context 'user', meaning that you can set it during a session and run a "vacuum freeze" with the modified setting. Giuseppe. I gather, then, that vacuum alone is enough. It'll scan the entire table and if it finds something wonky it'll freeze it. A "vacuum freeze" would be over kill and might freeze a bunch of stuff that isnt broken. (It wouldn't hurt to freeze it, but I have enough IO at the moment and just wanna fix whats broken). For now I'm only doing vacuum's, so hopefully that's enough. Thanks for the help. -Andy -- 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] vacuum_freeze_table_age for 9.3.2
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Il 05/12/2013 17:16, Andy Colson ha scritto: > The docs say vacuum, but the param is vacuum_freeze_table_age, so > do I need to "vacuum freeze" all the tables, or is vacuum enough? > > Also, will "set vacuum_freeze_table_age = 0; vacuum freeze;" work, > or do I need to modify the postgresql.conf and reload? Setting vacuum_freeze_table_age to 0 forces VACUUM to always scan all pages, effectively ignoring the visibility map. In this way a scan of the whole table is done, ensuring all old XIDs are replaced by FrozenXID. vacuum_freeze_table_age is a parameter with context 'user', meaning that you can set it during a session and run a "vacuum freeze" with the modified setting. Giuseppe. - -- Giuseppe Broccolo - 2ndQuadrant Italy PostgreSQL Training, Services and Support giuseppe.brocc...@2ndquadrant.it | www.2ndQuadrant.it -BEGIN PGP SIGNATURE- Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBCAAGBQJSoLfwAAoJELtGpyN/y3je278QAJomNDxax15x2MCxT4hoXyFA pJX4z7H7QjHXILcZ1ha5ajRvWrEtJEy3VvAZ2VBwAmk/VhAfcNAkU1tWA61K6UYD 47Npu1NhOzjDI9j9AfdA9cr/p3b/HRyx2qxjda6jBfhhLiDSZbQZXocC+FZicwtn qn3QQPpx6Ty9rt18OkliP9TKKjwm3tDGz1goOa58pbeH3TAjCipdLR+6Fn9WGM9b XLvNTMsHB3157VS7A6CjRRKvLj8Dxj/JoWvQC0q8ROZ728IpaEh+EqjeMmKoF8G2 /7xFyHFLINgUyAqVpv1scua5pO9RBYTsy4NCdnxLeGuSJt/ucfm+EkazNAMHLAj8 Vyq/lT9XUN2SB7OALa79TTcB+gFYrD+6yswBExnutTUZUP7gFrB6X7PFnmKxVPVO 2ovBzTXGcLRGp9lRoaJ/gwWRkyv1oKW39vYT6UBUjFM2NwGizX0z6BMd2vRC93r/ wq8Ll8KCMOe8vE/6qhnFMQ90pfJm+Zv4yT4CtdN5eP0b2Os6vVZVu9EMHgGtii0l ribqPQqmczySssdOSpTnlnq00du8moG7M5KCktRcuVsbDUpBS8yAbb2b4ccLxW/S VmSn2TuRWim/4oOfi1fsZJMB6ihmIv+e6qXjNeyZNZQI19dDxfnvfey5Wt3g0SnH mwqOBqn/DMia79z0048K =pp71 -END PGP SIGNATURE- -- 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] vacuum_freeze_table_age for 9.3.2
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Il 05/12/2013 17:16, Andy Colson ha scritto: Setting vacuum_freeze_table_age to 0 forces VACUUM to always scan all pages, effectively ignoring the visibility map. In this way a scan of the whole table is done, ensuring all old XIDs are replaced by FrozenXID. vacuum_freeze_table_age is a parameter with context 'user', meaning that you can set it during a session and run a "vacuum freeze" with the modified setting. Giuseppe. - -- Giuseppe Broccolo - 2ndQuadrant Italy PostgreSQL Training, Services and Support giuseppe.brocc...@2ndquadrant.it | www.2ndQuadrant.it -BEGIN PGP SIGNATURE- Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBCAAGBQJSoLfqAAoJELtGpyN/y3jedG4P/0yaz4RpHoKT6mm53rT1KrRC 5+UEKffji5P8x1cPWbkUyK8jvSbODByWHMqhAmMtDo5+Mdc9XW/MXrfJ7aJINVU7 rVI/VUaHRwJmwxQSKR0FM01Gqgjgx8W0mAcmIpk4nZpus+OH/8Ib6ImO58iebSaL 2E/ii7itZovtp816n2Pffk2HrftDfBCAQ1/XxQksTW8uGXRRx3qpF/EJm6lqsv4d xNxlOFjCqDM0j3poPnJaeprFyeWgzCiUCyg75NoW8na0VI7QunpwEI99QP7XcFmi drvx2uipzoajBSUOnPvtGSWRnJAoP7jTg4nooPDxn0DvMrf3YoqQ7xK4fKV0psQX xoXTF1IAiI6EW0Tr2uGoO4akWjiO0Yo4grLJ8W9oYca29Ai+qwtOA46qyyMmZrtm c9p+B1mK0DWwkgLpyuJJYYAvTyiCS+h9iLrooiIQdz/7bC0GlwjMKZo8rGvHFy6M RPCHi2w7Uw4emj0E0/ZpeeHhA2Aa8wJKUS/uoR8I+MTgf8VeVjOY3hOWuUHS/PNa S1XsrzfYp5z3uQ4dzMbVbdvgG6q7DqS8qSb+JocUg1HFRzdWFcgEE9M/0mLJOlDL 0VAwhpYPG/UBX0gXQkGoFavBorYA2vTuedKaKpCQuxtFxeaaKfbYPPfU/Ib1/6fP AwQUxlTBde8aC0ATVi5+ =WGwy -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] vacuum_freeze_table_age for 9.3.2
The docs say vacuum, but the param is vacuum_freeze_table_age, so do I need to "vacuum freeze" all the tables, or is vacuum enough? Also, will "set vacuum_freeze_table_age = 0; vacuum freeze;" work, or do I need to modify the postgresql.conf and reload? -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help with upsert
Richard Dunks wrote: > I will run this command 7 times with different daily table. > [ ... ] a WHERE NOT EXISTS (SELECT * FROM upsert b WHERE a.firewall [ ... ] > > When I run the command I get an error > ERROR: column reference "firewall" is ambiguous > LINE 3: ... a WHERE NOT EXISTS (SELECT * FROM upsert b WHERE b.firewall... > > Any idea what I am doing wrong? You are not showing us the actual query or the actual error message. (Note the mismatch in aliases qualifying "firewall" near the ends of the above lines.) Please cut and paste the entire query and the entire error message, and make sure they are from the same run. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [GENERAL] Re: [GENERAL] Fwd: Help！Why CPU Usage and LoadAverage Jump up Suddenly
Is khugepaged running during the stalls? http://www.postgresql.org/message-id/20130716195834.8fe5c79249cb2ff0d4270...@yahoo.es Matt On Thu, Dec 5, 2013 at 7:44 AM, Scott Marlowe wrote: > On Thu, Dec 5, 2013 at 1:46 AM, 吕晓旭 wrote: >> >> >> >> Hi, all >> We find so weird problem on our productive PostgreSQL system. And I >> don't know how could I do to resolve this problem. >> We deployed PostgreSQL 9.2.4 on two system environments, and the >> performances between them are absolutely different. one of them it's >> perfect, and the other one lets me down, CPU Usage and LoadAverage Jumped up >> Suddenly when concurrency smoothly rising up, simultaneously, average >> response time become unacceptable. >> Anyone, who could give me some advice? >> >> The parameters of system environment and PotgreSQL listed below: > > Have you tried monitoring your IO subsystem when this happens? I'd be > interested in iostat, vmstat, iotop, and so on to see what the IO > looks like. > > > -- > 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
[GENERAL] Re: [GENERAL] Fwd: Help！Why CPU Usage and LoadAverage Jump up Suddenly
On Thu, Dec 5, 2013 at 1:46 AM, 吕晓旭 wrote: > > > > Hi, all > We find so weird problem on our productive PostgreSQL system. And I don't > know how could I do to resolve this problem. > We deployed PostgreSQL 9.2.4 on two system environments, and the > performances between them are absolutely different. one of them it's perfect, > and the other one lets me down, CPU Usage and LoadAverage Jumped up Suddenly > when concurrency smoothly rising up, simultaneously, average response time > become unacceptable. > Anyone, who could give me some advice? > > The parameters of system environment and PotgreSQL listed below: Have you tried monitoring your IO subsystem when this happens? I'd be interested in iostat, vmstat, iotop, and so on to see what the IO looks like. -- 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] [Q] Update from a table
On 5.12.2013 15:14, Tom Lane wrote: > Ladislav Lenart writes: >> What happens if I issue UPDATE SET FROM... but with incomplete/buggy WHERE >> condition and thus SEVERAL rows from the from_list match ONE row to update? > > Any given row will be updated at most once. However, the from_list row > it gets updated against will be unpredictable, depending on the > implementation of the join. > > regards, tom lane Makes perfect sense. Thank you, Ladislav Lenart -- 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] [Q] Update from a table
Ladislav Lenart writes: > What happens if I issue UPDATE SET FROM... but with incomplete/buggy WHERE > condition and thus SEVERAL rows from the from_list match ONE row to update? Any given row will be updated at most once. However, the from_list row it gets updated against will be unpredictable, depending on the implementation of the join. 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
[GENERAL] [Q] Update from a table
Hello. What happens if I issue UPDATE SET FROM... but with incomplete/buggy WHERE condition and thus SEVERAL rows from the from_list match ONE row to update? Thank you in advance, Ladislav Lenart -- 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] Postgres 9.3 read block error went into recovery mode
Thanks everyone for the reply. So I would conclude it as OpenVZ problem, probably we will run some further check just to make sure no data corruption. Many thanks again :) On Thu, Dec 5, 2013 at 12:23 AM, Kevin Grittner wrote: > Albe Laurenz wrote: > > Shuwn Yuan Tee wrote: > > > >> We recently experienced crash on out postgres production server. > >> Here's our server environment: > > >> - in OpenVZ container > > >> ERROR: could not read block 356121 in file "base/33134/33598.2": Bad > address > >> > >> LOG: server process (PID 21119) was terminated by signal 7: Bus error > > > Unless my math is off, a PostgreSQL disk file should not contain > > more than 131072 blocks (1GB / 8KB), so something is whacky > > there. > > Not at all; the block number is the logical block number within the > relation; it determines both the segment to read from (in this case > ".2") and the offset into that segment. That all looks fine. > > > I am no hardware guy, but I believe that a bus error would > > indicate a hardware problem. > > Or a VM problem. Personally I have never seen this except in a VM, > and the cause always turned out to be a VM bug. Be sure you are > up-to-date on bug fixes for the software. > > -- > Kevin Grittner > EDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company >
Re: [GENERAL] Similarity search for sentences
May be totally a bad idea : explode your sentence into(sentence_number, one_word), n times , (makes a big table, you may want to partition) then, classic index on sentence number, and on the one world (btree if you make = comparison , more subtel if you do "like 'word' ") depending on perf, it could be wort it to regroup by words : sentence_number[], on_word Then you could try array or hstore on sentence_number[] ? Cheers, Rémi-C 2013/12/5 Janek Sendrowski > Hi, > > I have tables with millions of sentences. Each row contains a sentence. It > is natural language and every language is possible, but the sentences of > one table have the same language. > I have to do a similarity search on them. It has to be very fast, > because I have to search for a few hundert sentences many times. > The search shouldn't be context-based. It should just get sentences with > similar words(maybe stemmed). > > I already had a try with gist/gin-index-based trigramm search (pg_trgm > extension), fulltextsearch (tsearch2 extension) and a pivot-based indexing > (Fixed Query Array), but it's all to slow or not suitable. > Soundex and Metaphone aren't suitable, as well. > > I'm already working on this project since a long time, but without any > success. > Do any of you have an idea? > > I would be very thankful for help. > > Janek Sendrowski > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
[GENERAL] Similarity search for sentences
Hi, I have tables with millions of sentences. Each row contains a sentence. It is natural language and every language is possible, but the sentences of one table have the same language. I have to do a similarity search on them. It has to be very fast, because I have to search for a few hundert sentences many times. The search shouldn't be context-based. It should just get sentences with similar words(maybe stemmed). I already had a try with gist/gin-index-based trigramm search (pg_trgm extension), fulltextsearch (tsearch2 extension) and a pivot-based indexing (Fixed Query Array), but it's all to slow or not suitable. Soundex and Metaphone aren't suitable, as well. I'm already working on this project since a long time, but without any success. Do any of you have an idea? I would be very thankful for help. Janek Sendrowski -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: Help！Why CPU Usage and LoadAverage Jump up Suddenly
try again 2013/12/5 吕晓旭 > > delete monitor image >> >> >> 2013/12/5 吕晓旭 >> >>> >>> >>> Hi, all >>> We find so weird problem on our productive PostgreSQL system. And I >>> don't know how could I do to resolve this problem. >>> We deployed PostgreSQL 9.2.4 on two system environments, and the >>> performances between them are absolutely different. one of them it's >>> perfect, and the other one lets me down, CPU Usage and LoadAverage Jumped >>> up Suddenly when concurrency smoothly rising up, simultaneously, average >>> response time become unacceptable. >>> Anyone, who could give me some advice? >>> >>> The parameters of system environment and PotgreSQL listed below: >>> >>>- QPS Chart >>>- >>> >>> >>>- performance perfect on >>> - system environment >>> >>> $ uname -a >>> Linux l-interdb3.f.cn1 *2.6.18-238.19.1.el5* #1 SMP Fri Jul 15 >>> 07:31:24 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux >>> >>> $ cat /etc/redhat-release *CentOS release 5.6 (Final)* >>> >>> $ free -m >>>total used free sharedbuffers >>> cached >>> Mem: 96678 95620 1058 0443 >>> 84607 >>> -/+ buffers/cache: 10568 86109 >>> Swap:49151 0 49151 >>> >>> $ cat /proc/cpuinfo >>> processor : 23 >>> vendor_id : GenuineIntel >>> cpu family : 6 >>> model : 44 >>> model name : *Intel(R) Xeon(R) CPU E5645 @ 2.40GHz* >>> stepping: 2 >>> cpu MHz : 2400.146 >>> cache size : 12288 KB >>> physical id : 1 >>> siblings: 12 >>> core id : 9 >>> cpu cores : 6 >>> apicid : 51 >>> fpu : yes >>> fpu_exception : yes >>> cpuid level : 11 >>> wp : yes >>> flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr >>> pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall >>> nx pdpe1gb rdtscp lm constant_tsc ida nonstop_tsc arat pni monitor ds_cpl >>> vmx smx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2 popcnt lahf_lm >>> bogomips: 4800.10 >>> clflush size: 64 >>> cache_alignment : 64 >>> address sizes : 40 bits physical, 48 bits virtual >>> power management: [8] >>> >>> - pg_config >>> >>> BINDIR = /opt/pg92/bin >>> DOCDIR = /opt/pg92/share/doc/postgresql >>> HTMLDIR = /opt/pg92/share/doc/postgresql >>> INCLUDEDIR = /opt/pg92/include >>> PKGINCLUDEDIR = /opt/pg92/include/postgresql >>> INCLUDEDIR-SERVER = /opt/pg92/include/postgresql/server >>> LIBDIR = /opt/pg92/lib >>> PKGLIBDIR = /opt/pg92/lib/postgresql >>> LOCALEDIR = /opt/pg92/share/locale >>> MANDIR = /opt/pg92/share/man >>> SHAREDIR = /opt/pg92/share/postgresql >>> SYSCONFDIR = /opt/pg92/etc/postgresql >>> PGXS = /opt/pg92/lib/postgresql/pgxs/src/makefiles/pgxs.mk >>> CONFIGURE = '--prefix=/opt/pg92' '--with-perl' '--with-libxml' >>> '--with-libxslt' '--with-ossp-uuid' 'CFLAGS= -march=core2 -O2 ' >>> CC = gcc >>> CPPFLAGS = -D_GNU_SOURCE -I/usr/include/libxml2 >>> CFLAGS = -march=core2 *-O2 * -Wall -Wmissing-prototypes >>> -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels >>> -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv >>> CFLAGS_SL = -fpic >>> LDFLAGS = -Wl,-rpath,'/opt/pg92/lib',--enable-new-dtags >>> LDFLAGS_EX = >>> LDFLAGS_SL = >>> LIBS = -lpgport -lxslt -lxml2 -lz -lreadline -ltermcap -lcrypt -ldl >>> -lm >>> VERSION = PostgreSQL 9.2.4 >>> >>> - performace >>> - >>> - >>> >>> >>>- performance weird >>> - system environment(kernel 2.6.32-220 is also tested, no >>> different on performance) >>> >>> $ uname -a >>> Linux l-interdb11.f.cn1 *3.2.34-1.el6.x86_64* #1 SMP Mon Jan 14 >>> 18:23:19 CST 2013 x86_64 x86_64 x86_64 GNU/Linux >>> >>> $ cat /etc/redhat-release *CentOS release 6.2 (Final)* >>> >>> $ free -m >>>total used free sharedbuffers >>> cached >>> Mem: 64387 62907 1479 0182 >>> 56524 >>> -/+ buffers/cache: 6200 58186 >>> Swap:49151494 48657 >>> >>> $ cat /proc/cpuinfo >>> processor : 23 >>> vendor_id : GenuineIntel >>> cpu family: 6 >>> model : 45 >>> model name: *Intel(R) Xeon(R) CPU E5-2630 0 @ 2.30GHz* >>> stepping : 7 >>> microcode : 0x70d >>> cpu MHz : 2300.020 >>> cache size: 15360 KB >>> physical id : 1 >>> siblings : 12 >>> core id : 5 >>> cpu cores : 6 >
