date:20130221

On Thu, Feb 21, 2013 at 6:06 AM, Ivan Voras ivo...@freebsd.org wrote:
 On 21/02/2013 12:52, Ivan Voras wrote:

 I'd like to use pg_trgm for matching substrings case-insensitively, but
 it doesn't seem to use the index:


 As a sort-of followup, the '%' operator kind of works but takes
 incredibly long time, and the selectivity estimates are completely wrong:

 nn= vacuum analyze documents;
 VACUUM
 nn= explain select id,title from documents where raw_data % 'zagreb';
QUERY PLAN

 -
  Bitmap Heap Scan on documents  (cost=128.42..330.87 rows=54 width=108)
Recheck Cond: ((raw_data)::text % 'zagreb'::text)
-  Bitmap Index Scan on documents_raw_data_trgm  (cost=0.00..128.40
 rows=54 width=0)
  Index Cond: ((raw_data)::text % 'zagreb'::text)
 (4 rows)

 nn= explain analyze select id,title from documents where raw_data %
 'zagreb';
QUERY
 PLAN
 -
  Bitmap Heap Scan on documents  (cost=128.42..330.87 rows=54 width=108)
 (actual time=98750.283..98750.283 rows=0 loops=1)
Recheck Cond: ((raw_data)::text % 'zagreb'::text)
-  Bitmap Index Scan on documents_raw_data_trgm  (cost=0.00..128.40
 rows=54 width=0) (actual time=26.748..26.748 rows=51874 loops=1)
  Index Cond: ((raw_data)::text % 'zagreb'::text)
  Total runtime: 98750.623 ms
 (5 rows)


 There is no IO load during this query.

pg_trgm is not really designed for indexing large documents, but for
fuzzy simple string (company name, address, etc) matching.   probably
better off with full text search.

merlin


-- 
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] subselects vs WITH in views

On Thu, Feb 21, 2013 at 4:31 AM, Seref Arikan serefari...@gmail.com wrote:
 Hi Merlin,
 So should I interpret this as: there is a potential gain from choosing
 subqueries over with WITHs ?

Well, potentially, yes.  WITH is a mechanic to force iterative order
of evaluation on queries.  This can be a good or bad thing naturally.
Subqueries can also do this, especially if you put them in the field
select list -- but WITH is more general.  We also have an undocumented
hack that uses OFFSET 0 to force subquery evaluation.  These are all
very dangerous tools because they tend to be very sensitive to data
inputs as you are bypassing database statistics effectively.  The
other end of the spectrum is to use vanilla JOINs as much as possible
-- this releases the work of planning the query to the database.

Upcoming 9.3 LATERAL will remove one large class of cases where we
have to do this: joining against set returning functions with
non-constant inputs.

merlin


-- 
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] Trigram (pg_trgm) GIN index not used

Ivan Voras ivo...@freebsd.org writes:
 I have a table with the following structure:
 ...
  raw_data  | citext   | not null
 ...
 documents_raw_data_trgm gin (raw_data gin_trgm_ops)

 I'd like to use pg_trgm for matching substrings case-insensitively, but
 it doesn't seem to use the index:

You're outsmarting yourself by using citext as the column datatype.
That causes ilike to be interpreted as a citext-specific operator,
which is not a member of the gin_trgm_ops operator class, so it doesn't
match this index.

I wonder whether we really need that citext-specific operator at all
... but in the meantime, if you need the column to be citext for some
other reason, I'd suggest making a gin index on raw_data::text and
then writing the query as raw_data::text ilike '%zagreb%'.

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] Trigram (pg_trgm) GIN index not used

On Thu, Feb 21, 2013 at 9:57 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Ivan Voras ivo...@freebsd.org writes:
 I have a table with the following structure:
 ...
  raw_data  | citext   | not null
 ...
 documents_raw_data_trgm gin (raw_data gin_trgm_ops)

 I'd like to use pg_trgm for matching substrings case-insensitively, but
 it doesn't seem to use the index:

 You're outsmarting yourself by using citext as the column datatype.
 That causes ilike to be interpreted as a citext-specific operator,
 which is not a member of the gin_trgm_ops operator class, so it doesn't
 match this index.

 I wonder whether we really need that citext-specific operator at all
 ... but in the meantime, if you need the column to be citext for some
 other reason, I'd suggest making a gin index on raw_data::text and
 then writing the query as raw_data::text ilike '%zagreb%'.

hm, one more data point that citext implementation didn't succeed in
terms of abstracting you from case sensitivity issues.

merlin


-- 
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] Trigram (pg_trgm) GIN index not used

Merlin Moncure mmonc...@gmail.com writes:
 On Thu, Feb 21, 2013 at 9:57 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 I wonder whether we really need that citext-specific operator at all
 ... but in the meantime, if you need the column to be citext for some
 other reason, I'd suggest making a gin index on raw_data::text and
 then writing the query as raw_data::text ilike '%zagreb%'.

 hm, one more data point that citext implementation didn't succeed in
 terms of abstracting you from case sensitivity issues.

I think this is just a bug and not a fundamental design flaw: it looks
to me like simply removing the citext-specific declarations of the regex
operators would fix the problem (because then it'd fall back on the
standard operators with an implicit cast to text, and that would match
the index).  Might cause a problem though for anybody who's got those
operators embedded in views.

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] FATAL logged when starting

2013-02-21 Thread Sahagian, David

We get this FATAL pg_log entry, just after Postgres startup.
(Postgres does start OK)
//9.1.3 on Linux

2013-02-20 10:15:46.637 EST   50eedb22.3602  0
  LOG:  database system is ready to accept connections
2013-02-20 10:15:46.637 EST   50eedb22.3607  0
  LOG:  autovacuum launcher started
2013-02-20 10:15:47.390 EST [unknown] rsaadmin 50eedb23.3609 2/1 0
  FATAL:  no pg_hba.conf entry for host [local], user rsaadmin, database 
postgres, SSL off

Postgres is run under the linux user account rsaadmin, but we don't have a 
Postgres role by that name.
We use [-w] as an option for pg_ctl start.

As I read postgresql.org/docs/9.1/static/app-pg-ctl.html,

  -w
  . . . When waiting for startup, pg_ctl repeatedly attempts to connect to the 
server . . .


My question:
Is there a way for me to specify a DIFFERENT USER, that pg_ctl will use to 
determine postgres has started ?

If not, how do people work around getting this nasty FATAL log message ?

Thanks,
-dvs-

Re: [GENERAL] FATAL logged when starting

Sahagian, David david.sahag...@emc.com writes:
 We get this FATAL pg_log entry, just after Postgres startup.
   FATAL:  no pg_hba.conf entry for host [local], user rsaadmin, database 
 postgres, SSL off

 Postgres is run under the linux user account rsaadmin, but we don't have a 
 Postgres role by that name.

 Is there a way for me to specify a DIFFERENT USER, that pg_ctl will use to 
 determine postgres has started ?

I think you could set PGUSER in pg_ctl's environment.

 If not, how do people work around getting this nasty FATAL log message ?

You could just ignore it ... it's harmless.

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] Determining last auto vacuum / analyze

2013-02-21 Thread François Beausoleil

Hi all,

According to 
http://heatware.net/databases/how-when-postgres-tables-auto-vacuum-analyze/ 
(August 2010), running:

select relname,last_vacuum, last_autovacuum, last_analyze, last_autoanalyze 
from pg_stat_user_tables;

should tell me which tables have been auto vacuumed and auto analyzed. My 
schema has 300 tables, wheighs in at ~650 GiB on-disk and compresses to 70 GiB 
as a plain SQL dump. Running the above query + WHERE last_autoanalyze is not 
null or last_autovacuum is not null only returns 38 rows?! I see the 
autovacuum daemon work when polling pg_stat_activity. Something doesn't jive 
here...

I have not touched the autovacuum settings at all, so everything is at the 
default values (see details at bottom).

The application is on-line analytics with long reporting queries. 3/hour I 
import new data. Once a day, I rollup the raw values into summary tables and 
run reports on the summary values. One of the steps in each of the import and 
summary scripts is to ANALYZE the tables (not VACUUM, plain ANALYZE).

I suspect autovacuum / autoanalyze doesn't kick in frequently enough. Would you 
say this is true? What would be recommended settings given the above? First 
thing I'll do is set log_autovacuum_min_duration to 0 to see what's really 
going on.

Thanks!
François



 SELECT version(); -- Stock Ubuntu 12.04 PostgreSQL installed from apt
PostgreSQL 9.1.8 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 
4.7.2, 64-bit

$ cat /etc/postgresql/9.1/main/postgresql.conf 
data_directory = '/var/lib/postgresql/9.1/main'

listen_addresses = '*'
port = 5432
max_connections = 120

shared_buffers = 8GB
work_mem = 512MB

fsync = on
synchronous_commit = off
checkpoint_segments = 96
checkpoint_timeout = 10min
checkpoint_completion_target = 0.9
effective_cache_size = 12GB
maintenance_work_mem = 2GB

hot_standby = on
hot_standby_feedback = on

 SHOW all;
 autovacuum  | on   | Starts the 
autovacuum subprocess.
 autovacuum_analyze_scale_factor | 0.1  | Number of tuple 
inserts, updates or deletes prior to analyze as a fraction of reltuples.
 autovacuum_analyze_threshold| 50   | Minimum number of 
tuple inserts, updates or deletes prior to analyze.
 autovacuum_freeze_max_age   | 2| Age at which to 
autovacuum a table to prevent transaction ID wraparound.
 autovacuum_max_workers  | 3| Sets the maximum 
number of simultaneously running autovacuum worker processes.
 autovacuum_naptime  | 1min | Time to sleep 
between autovacuum runs.
 autovacuum_vacuum_cost_delay| 20ms | Vacuum cost delay 
in milliseconds, for autovacuum.
 autovacuum_vacuum_cost_limit| -1   | Vacuum cost 
amount available before napping, for autovacuum.
 autovacuum_vacuum_scale_factor  | 0.2  | Number of tuple 
updates or deletes prior to vacuum as a fraction of reltuples.
 autovacuum_vacuum_threshold | 50   | Minimum number of 
tuple updates or deletes prior to vacuum.



smime.p7s
Description: S/MIME cryptographic signature

[GENERAL] Full text and removing dashes from names

2013-02-21 Thread Asmir Mustafic


Hi everybody!

I have a little problem with postgres 9.0 full text functionalities.

I have a document containing this string: marko-jennifer/mary
I have to match that document with words like marko, jennifer or 
mary, but i can't find a way to do it.


Doing a dubug query i get:

select ts_lexize('public.dict', 'marko-jennifer/mary') , 
to_tsvector('public.generic', 'marko-jennifer')


return
ts_lexizeto_tsvector
{marko-jennifer/mary}''


I can't use language based  stemming because names  should not be be 
stemmed)


How can i replace - and / with spaces and obtain (also other symbols 
like dots, commas..):


select ts_lexize('public.dict', 'marko-jennifer/mary') , 
to_tsvector('public.generic', 'marko-jennifer/mary')


return
ts_lexizeto_tsvector
{marko jennifer mary}{marko,jennifer,mary}


?

PS: i can't do  things like select ts_lexize('public.dict', 
translate('marko-jennifer/mary', '-', ' ')) ...


Thanks in advice

Asmir


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Need help extripating plpgsql

I am trying, without success, to create a PG-9.2 database without
including the plpgsql extension. I have tried specifying template0
and the database is nonetheless created with plpgsql. I have deleted
plpgsql from template1 and the new database is nonetheless created
with plpgsql.

I desire to remove plpgsql from newly created databases because the
dump that is generated by pgdump contains this line:

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

And for some reason this statement causes my test suit to fail with
this error:

psql:/home/byrnejb/Projects/Software/theHeart/code/proforma_rails_3_2/db/structure.sql:22:
ERROR: must be owner of extension plpgsql

I do not understand why this happens since, as I read this, if the
plpgsql extension already exists in the database, which it does, then
this statement should not be executed at all. But it does. Further,
I do not understand why or how plpgsql is being included into
databases on create as I have removed it from template1 and it does
not exist in template0 to begin with.

I note that plpgsql has NO owner specified in the newly created
database, which mystifies me as well. Naively I thought that newly
created databases would have all of their components owned by the
database owner.

How do I resolve this problem?

--
*** E-Mail is NOT a SECURE channel ***
James B. Byrnemailto:byrn...@harte-lyne.ca
Harte Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] View to show privileges on views/tables/sequences/foreign tables

2013-02-21 Thread bricklen

A while back I was looking for a way to display object privileges
quickly with a bit better readibility. The following view is what I
came up with. Suggestions and improvements welcome (or comments
stating that there are much easi\er ways to get the same details).

(was created in a utility admin schema)

create or replace view admin.object_privileges as
select  objtype,
schemaname,
objname,
owner,
objuser,
privs,
string_agg(
(case   privs_individual
when 'arwdDxt' then 'All'
when '*' then 'Grant'
when 'r' then 'SELECT'
when 'w' then 'UPDATE'
when 'a' then 'INSERT'
when 'd' then 'DELETE'
when 'D' then 'TRUNCATE'
when 'x' then 'REFERENCES'
when 't' then 'TRIGGER'
when 'X' then 'EXECUTE'
when 'U' then 'USAGE'
when 'C' then 'CREATE'
when 'c' then 'CONNECT'
when 'T' then 'TEMPORARY'
else 'Unknown: '||privs end
), ', ' ORDER BY privs_individual) as privileges_pretty
from(select objtype,
schemaname,
objname,
owner,
privileges,
(case when coalesce(objuser,'') is not distinct from
'' then 'public' else objuser end)
|| (case when pr2.rolsuper then '*' else '' end)
as objuser,
privs,
(case   when privs in ('*','arwdDxt') then privs
else regexp_split_to_table(privs,E'\\s*')
end) as privs_individual
from(select distinct
objtype,
schemaname,
objname,
coalesce(owner,'') || (case when pr.rolsuper
then '*' else '' end) as owner,
regexp_replace(privileges,E'\/.*','') as privileges,

(regexp_split_to_array(regexp_replace(privileges,E'\/.*',''),'='))[1]
as objuser,

(regexp_split_to_array(regexp_replace(privileges,E'\/.*',''),'='))[2]
as privs
from(SELECT n.nspname as schemaname,
c.relname as objname,
CASE c.relkind WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view' WHEN 'S' THEN 'sequence' END as objtype,

regexp_split_to_table(array_to_string(c.relacl,','),',') as
privileges,
pg_catalog.pg_get_userbyid(c.relowner) as Owner
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid =
c.relnamespace
WHERE c.relkind IN ('r', 'v', 'S', 'f')
AND n.nspname !~ '(pg_catalog|information_schema)'
--AND pg_catalog.pg_table_is_visible(c.oid) /*
Uncomment to show only objects */
) as y  /*
visible in search path */
left join pg_roles pr on (pr.rolname = y.owner)
) as p2
left join pg_roles pr2 on (pr2.rolname = p2.objuser)
--where coalesce(p2.objuser,'') is distinct from '' /*
Uncomment to hide public role */
) as p3
group by objtype, schemaname,objname, owner, objuser, privs
order by objtype,schemaname,objname,objuser,privileges_pretty;

comment on column admin.object_privileges.owner is '* after the
owner indicates that the owner is a superuser';
comment on column admin.object_privileges.objuser is '* after the
objuser indicates that the objuser is a superuser';


select * from admin.object_privileges limit 10;

 objtype  | schemaname |objname  |   owner   |
objuser|  privs  |   privileges_pretty
--++-+---+-+-+
 sequence | public | event_id_seq| postgres* |
postgres*   | rwU | SELECT, USAGE, UPDATE
 sequence | public | event_id_seq| postgres* | foobar
| rw  | SELECT, UPDATE
 table| public | network_events  | postgres* |
postgres*   | arwdDxt | All
 table| public | network_events  | postgres* | foobar
| ar  | INSERT, SELECT
 table| public | network_events_201301   | postgres* |
postgres*   | arwdDxt | All
 table| public | network_events_201301   | postgres* | foobar
| arwd| INSERT, DELETE, SELECT, UPDATE
 table| public | network_events_201302   | postgres* |
postgres*   | arwdDxt | All
 table| public | network_events_201302   | postgres* | foobar
| arwd| INSERT, DELETE, SELECT, UPDATE
 table| public | network_events_20130211 | postgres* |
postgres*   | arwdDxt | All
 table| public | event   | postgres* | foobar
| ar*

Re: [GENERAL] Determining last auto vacuum / analyze

2013-02-21 Thread Jeff Janes

On Thu, Feb 21, 2013 at 8:52 AM, François Beausoleil franc...@teksol.info
wrote:


 The application is on-line analytics with long reporting queries. 3/hour
I import new data. Once a day, I rollup the raw values into summary tables
and run reports on the summary values. One of the steps in each of the
import and summary scripts is to ANALYZE the tables (not VACUUM, plain
ANALYZE).


If you run ANALYZE manually, and then the data doesn't change much after
that, then there is no reason for the tables to be autoanalyzed.

Cheers,

Jeff

Re: [GENERAL] Need help extripating plpgsql


On Thu, February 21, 2013 12:38, James B. Byrne wrote:
 I am trying, without success, to create a PG-9.2 database without
 including the plpgsql extension.  I have tried specifying template0
 and the database is nonetheless created with plpgsql.  I have deleted
 plpgsql from template1 and the new database is nonetheless created
 with plpgsql.

 I desire to remove plpgsql from newly created databases because the
 dump that is generated by pgdump contains this line:

 CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;


Wrong line.  This is the line

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

And yes, I went through this a year ago with PG-9.1 and resolved it
once by switching to template0 in the connection configuration. Now it
is back with PG-9.2. using the exact same configuration code because
evidently plpgsql is added regardless.


-- 
***  E-Mail is NOT a SECURE channel  ***
James B. Byrnemailto:byrn...@harte-lyne.ca
Harte  Lyne Limited  http://www.harte-lyne.ca
9 Brockley Drive  vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada  L8E 3C3



-- 
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 extripating plpgsql

On Thu, Feb 21, 2013 at 11:48 AM, James B. Byrne byrn...@harte-lyne.ca wrote:

 On Thu, February 21, 2013 12:38, James B. Byrne wrote:
 I am trying, without success, to create a PG-9.2 database without
 including the plpgsql extension.  I have tried specifying template0
 and the database is nonetheless created with plpgsql.  I have deleted
 plpgsql from template1 and the new database is nonetheless created
 with plpgsql.

 I desire to remove plpgsql from newly created databases because the
 dump that is generated by pgdump contains this line:

 CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;


 Wrong line.  This is the line

 COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

 And yes, I went through this a year ago with PG-9.1 and resolved it
 once by switching to template0 in the connection configuration. Now it
 is back with PG-9.2. using the exact same configuration code because
 evidently plpgsql is added regardless.

curious why you want to do this.  there was actually some debate back
in the day about pros/cons of having pl/pgsql be a built-in feature,
which as you can see is where things are going.

if you don't mind surgery with a shotgun, you can simply drop the
extension after the load resolves.

merlin


-- 
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] View to show privileges on views/tables/sequences/foreign tables

2013-02-21 Thread bricklen

On Thu, Feb 21, 2013 at 9:38 AM, bricklen brick...@gmail.com wrote:
 A while back I was looking for a way to display object privileges
 quickly with a bit better readibility. The following view is what I
 came up with. Suggestions and improvements welcome (or comments
 stating that there are much easi\er ways to get the same details).

The information schema exposes a lot of the same information, so it
might be more portable to query from there rather than using the
object_privileges view.
Eg. for table privileges:

select * from information_schema.table_privileges where table_name =
'event' limit 1;
-[ RECORD 1 ]--+-
grantor| postgres
grantee| PUBLIC
table_catalog  | testdb
table_schema   | public
table_name | event
privilege_type | INSERT
is_grantable   | YES
with_hierarchy | NO


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Database corruption event, unlockable rows, possibly bogus virtual xids? Invalid backend server xid

2013-02-21 Thread Ned Wolpert

(I originally posted this to pgsql-admin and was pointed to here instead.)

Folks-

  I'm doing a postmortem on a corruption event we had. I have an idea on
what happened, but not sure. I figure I'd share what happened and see if
I'm close to right here.

  Event: Running 9.1.6 with hot-standby, archiving 4 months of wal files,
and even a nightly pg_dump all. 50G database.  Trying to update or delete a
row in a small (21 row, but heavily used table) would lock up completely.
Never finish. Removed all clients, restarted the db instance, no joy. Check
pg_stat_activity, and nothing that wasn't idle run the delete, locked
up.

  Ran (SELECT*FROM pg_class JOIN pg_locks ON pg_locks.relation =
pg_class.oid;) with no clients touching this row, fresh restart of the db,
and saw virtualtransactions against this same table where the values would
be -1/n were n was a huge number.  Turns out we had about 159
entries from different tables in the database.  Checked hot-standby and, of
course, no locks or anything.  Switched to hot-standby.

  Hot-standby instantly gained these locks, Also noticed that 2 prepared
transactions migrated to the hot-standby. Binary upgraded to 9.1.8, locks
still existed. Ended up executing the one thing we knew would work. Take
the site down, pg_dumpall to fresh instance. Everything is fine.

A little more background: We were running 9.1.4 back when 9.1.6 came out.
We saw there was possible corruption issues and did a binary upgrade and
reindexing. Everything seemed to be fine, but I wonder if we really had
problems back then. We rebuilt the hot-standby after the binary upgrade via
normal restore and wal-file replays.  I should also note that this row that
had the lock on it that would not go away, was created by an app server
that was killed (via -9) since it was non-responsive, and the row 'create
date' (in db and logs) is the exact time the app server was killed.

I was wondering if a) these virtualtransactions that start with '-1/'
indicate a problem, b) if this could have happened from pre 9.1.6
corruption that was fixed in 9.1.6. Or, could this have occurred when we
killed that app server? Or am I looking in the wrong place.

I do still have the old data directories so I can start them up and check
out the dataset. Any advice?

Currently running environment: CentOS 5.x
Used the http://yum.postgresql.org/9.1 repo...
Currently using PostgreSQL 9.1.8 on x86_64-unknown-linux-gnu, compiled by
gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit
but was on 9.1.6 at failure time.
The hardware on both primary and secondary servers are in good condition,
raid5 via a hardware card with no issues.  Each a 2x4x2 (2 hyperthreaded
Xeon E5620 2.4Ghz, 4 cores each, 16 threads total) 32G Ram.

Data from pg_dumpall checked out fine; no data loss occurred that we could
tell. Just this transaction lock and what seemed like invalid backend ids
listed in the virtualtransaction from the pg_locks table/view.
(-1/verylargenumber)






-- 
Virtually, Ned Wolpert

Settle thy studies, Faustus, and begin...   --Marlowe

Re: [GENERAL] Full text and removing dashes from names

2013-02-21 Thread Kevin Grittner

Asmir Mustafic as...@lignano.it wrote:

 I can't use language based  stemming because names  should not be
 be stemmed)

If you have a column that explicitly contains names, I recommend
trigram similarity searching.  I have found trigram similarity much
better than document-oriented full text searches, LIKE, or regular
expressions for names.  If you want a general document search that
uses some special rules in addition to what you get out of the
dictionaries, I have had good results picking out relavent parts
using regular expressions, building that into a string and casting
it to tsvector, then concatenating that tsvector with what came
from the lexeme/dictionary evaluation.

-- 
Kevin Grittner
EnterpriseDB: 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] Need help extripating plpgsql


On Thu, February 21, 2013 13:23, Merlin Moncure wrote:
 On Thu, Feb 21, 2013 at 11:48 AM, James B. Byrne
 byrn...@harte-lyne.ca wrote:

 On Thu, February 21, 2013 12:38, James B. Byrne wrote:
 I am trying, without success, to create a PG-9.2 database without
 including the plpgsql extension.  I have tried specifying template0
 and the database is nonetheless created with plpgsql.  I have
 deleted plpgsql from template1 and the new database is
 nonetheless created with plpgsql.

 I desire to remove plpgsql from newly created databases because the
 dump that is generated by pgdump contains this line:

 CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;


 Wrong line.  This is the line

 COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

 And yes, I went through this a year ago with PG-9.1 and resolved it
 once by switching to template0 in the connection configuration. Now
 it is back with PG-9.2. using the exact same configuration code
 because evidently plpgsql is added regardless.

 curious why you want to do this.  there was actually some debate back
 in the day about pros/cons of having pl/pgsql be a built-in feature,
 which as you can see is where things are going.


I want to do this because my automated test harness is choking because
it cannot add an absolutely worthless COMMENT to that extension.  It
cannot add the comment because the language extension is added to the
database with an incorrect owner.  A database created by userid X
should, in ALL RESPECTS, be OWNED by userid X.  When the ownership of
database Y is changed from user A to user B then all of the attributes
of database Y should become owned by B.  For some reason this is not
the case with the plpgsql language extension.

 if you don't mind surgery with a shotgun, you can simply drop the
 extension after the load resolves.

I have tried this and it does not work.  It does not work for the
simple reason that the test harness recreates the test database from
the dump file each run.  The dump file is created with a COMMENT
statement which cannot be applied to the plpgsql language extension
statement unless the user that connects to the database is a
superuser.  That condition makes the granting of DBCREATE to another
userid somewhat pointless.


I have resolved this by:

1. as the postgres user creating a copy of template1 (template2)

2. as the postgres user assigning the test userid as owner of template2

3. as the postgres user dropping the plpgsql extension from template2

4. as the test user adding the plpgsql extension to template2

5. specifying template2 in the database connection configuration file.

I believe, however, that this entire situation is a defect in
postgresql-9.2 and 9.1.  The plpgsql language extension should not be
included in new databases if it does not already exist in the selected
template or when no template is used at all.  Surely the local DBA is
the final arbiter of what a given installation wishes to have in their
databases and forcing them to go through hoops to accomplish this is
hardly user-friendly.

Further, if a language, or for that matter any, extension is added to
a new database from a template or other source then that extension
should be owned by the owner of the resulting database and not by any
other userid.  If there is a good reason as to why this should be
otherwise I would certainly like to have it explained to me.

The current arrangement is not really satisfactory as it requires
either separate template databases for each userid granted the
DBCREATE role or the superuser role has to be granted in place of the
DBCREATE role.



-- 
***  E-Mail is NOT a SECURE channel  ***
James B. Byrnemailto:byrn...@harte-lyne.ca
Harte  Lyne Limited  http://www.harte-lyne.ca
9 Brockley Drive  vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada  L8E 3C3



-- 
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] Database corruption event, unlockable rows, possibly bogus virtual xids? Invalid backend server xid

2013-02-21 Thread Kevin Grittner

Ned Wolpert ned.wolp...@imemories.com wrote:

 I'm doing a postmortem on a corruption event we had. I have an
 idea on what happened, but not sure. I figure I'd share what
 happened and see if I'm close to right here.

 Running 9.1.6 with hot-standby, archiving 4 months of wal files,
 and even a nightly pg_dump all.

Those WAL files aren't going to be of much use without an
occasional base backup to apply them to.

 50G database.  Trying to update or delete a row in a small (21
 row, but heavily used table) would lock up completely. Never
 finish.

How long is never in hours or days?

 Removed all clients, restarted the db instance, no joy.
 Check pg_stat_activity, and nothing that wasn't idle run the
 delete, locked up.

Did you have any rows in pg_prepared_xacts that had lingered for
longer than you were waiting for the delete?

 Ran (SELECT*FROM pg_class JOIN pg_locks ON pg_locks.relation =
 pg_class.oid;) with no clients touching this row

Of course you need to be connected to the right database when you
run this, and you need to look at relation locks -- row locks don't
show up in that view.

 fresh restart of the db, and saw virtualtransactions against this
 same table where the values would be -1/n were n was a
 huge number.  Turns out we had about 159 entries from different
 tables in the database.

Sounds like prepared transactions.  Again, what was in
pg_prepared_xacts?

 Checked hot-standby and, of course, no locks or anything.
 Switched to hot-standby.

Meaning you promoted it to master?

 Hot-standby instantly gained these locks, Also noticed that 2
 prepared transactions migrated to the hot-standby. Binary
 upgraded to 9.1.8, locks still existed. Ended up executing the
 one thing we knew would work. Take the site down, pg_dumpall to
 fresh instance. Everything is fine.

That all sounds consistent with a flaky transaction manager.

 A little more background: We were running 9.1.4 back when 9.1.6
 came out. We saw there was possible corruption issues and did a
 binary upgrade and reindexing. Everything seemed to be fine, but
 I wonder if we really had problems back then. We rebuilt the
 hot-standby after the binary upgrade via normal restore and
 wal-file replays.  I should also note that this row that had the
 lock on it that would not go away, was created by an app server
 that was killed (via -9) since it was non-responsive, and the row
 'create date' (in db and logs) is the exact time the app server
 was killed.

It sounds as though you abruptly terminated the process running
your transaction manager, which left it unaware of one or more
dangling prepared transactions.  Further, it sounds like your
transaction manager doesn't go looking for such detritus.  If it's
not going to watch for such problems, you had better do so.  Any
prepared transaction which is sitting in pg_prepared_xacts for more
than a few seconds, I would consider suspect.  After a few minutes
hours I would consider them to be a problem.  After a day I would
consider the transaction manager to have fallen on its face, and
would go clean things up by either committing or rolling back the
prepared transaction(s).

 I was wondering if a) these virtualtransactions that start with
 '-1/' indicate a problem, b) if this could have happened from pre
 9.1.6 corruption that was fixed in 9.1.6. Or, could this have
 occurred when we killed that app server? Or am I looking in
 the wrong place.

-1 as the process doesn't indicate a problem per se.  It indicates
the transaction has been prepared and is no longer associated
with a backend process or connection.

Something which was assuming the role of a transaction manager told
a transaction (or many of them) to prepare for commit as part of a
distributed transaction.  A transaction which says it successfully
completed such a PREPARE statement must hold all its locks and keep
all changes pending until it is told to commit or roll back, even
across database restarts.  It sounds like things were left in this
state for a very long time, which can lead to all kinds of
problems, notably bloat and blocking.

 I do still have the old data directories so I can start them up
 and check out the dataset. Any advice?

I would start it up and see what's in pg_prepared_xacts.

-- 
Kevin Grittner
EnterpriseDB: 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] Need help extripating plpgsql


On 02/21/2013 09:38 AM, James B. Byrne wrote:

I am trying, without success, to create a PG-9.2 database without
including the plpgsql extension.  I have tried specifying template0
and the database is nonetheless created with plpgsql.  I have deleted
plpgsql from template1 and the new database is nonetheless created
with plpgsql.

I desire to remove plpgsql from newly created databases because the
dump that is generated by pgdump contains this line:

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

And for some reason this statement causes my test suit to fail with
this error:

psql:/home/byrnejb/Projects/Software/theHeart/code/proforma_rails_3_2/db/structure.sql:22:
ERROR:  must be owner of extension plpgsql

I do not understand why this happens since, as I read this, if the
plpgsql extension already exists in the database, which it does, then
this statement should not be executed at all.  But it does.  Further,
I do not understand why or how plpgsql is being included into
databases on create as I have removed it from template1 and it does
not exist in template0 to begin with.


Actually it does exist in template0.




--
Adrian Klaver
adrian.kla...@gmail.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] Need help extripating plpgsql


On 02/21/2013 12:14 PM, James B. Byrne wrote:


On Thu, February 21, 2013 13:23, Merlin Moncure wrote:

On Thu, Feb 21, 2013 at 11:48 AM, James B. Byrne
byrn...@harte-lyne.ca wrote:


On Thu, February 21, 2013 12:38, James B. Byrne wrote:

I am trying, without success, to create a PG-9.2 database without
including the plpgsql extension.  I have tried specifying template0
and the database is nonetheless created with plpgsql.  I have
deleted plpgsql from template1 and the new database is
nonetheless created with plpgsql.

I desire to remove plpgsql from newly created databases because the
dump that is generated by pgdump contains this line:

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;



Wrong line.  This is the line

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

And yes, I went through this a year ago with PG-9.1 and resolved it
once by switching to template0 in the connection configuration. Now
it is back with PG-9.2. using the exact same configuration code
because evidently plpgsql is added regardless.


curious why you want to do this.  there was actually some debate back
in the day about pros/cons of having pl/pgsql be a built-in feature,
which as you can see is where things are going.



I want to do this because my automated test harness is choking because
it cannot add an absolutely worthless COMMENT to that extension.  It
cannot add the comment because the language extension is added to the
database with an incorrect owner.  A database created by userid X
should, in ALL RESPECTS, be OWNED by userid X.  When the ownership of
database Y is changed from user A to user B then all of the attributes
of database Y should become owned by B.  For some reason this is not
the case with the plpgsql language extension.


if you don't mind surgery with a shotgun, you can simply drop the
extension after the load resolves.


I have tried this and it does not work.  It does not work for the
simple reason that the test harness recreates the test database from
the dump file each run.  The dump file is created with a COMMENT
statement which cannot be applied to the plpgsql language extension
statement unless the user that connects to the database is a
superuser.  That condition makes the granting of DBCREATE to another
userid somewhat pointless.


I have resolved this by:

1. as the postgres user creating a copy of template1 (template2)

2. as the postgres user assigning the test userid as owner of template2

3. as the postgres user dropping the plpgsql extension from template2

4. as the test user adding the plpgsql extension to template2

5. specifying template2 in the database connection configuration file.


template1=# \dL
   List of languages
   Name|  Owner   | Trusted | Description
---+--+-+--
 plpgsql   | postgres | t   | PL/pgSQL procedural language
 plpythonu | postgres | f   |
(2 rows)


template1=# alter language plpgsql owner to aklaver;
ALTER LANGUAGE
template1=# \dL
   List of languages
   Name|  Owner   | Trusted | Description
---+--+-+--
 plpgsql   | aklaver  | t   | PL/pgSQL procedural language
 plpythonu | postgres | f   |
(2 rows)




The current arrangement is not really satisfactory as it requires
either separate template databases for each userid granted the
DBCREATE role or the superuser role has to be granted in place of the
DBCREATE role.




That is sort of the point of the template system, different templates 
for different situations.



--
Adrian Klaver
adrian.kla...@gmail.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] Database corruption event, unlockable rows, possibly bogus virtual xids? Invalid backend server xid

Ned Wolpert ned.wolp...@imemories.com writes:
   Event: Running 9.1.6 with hot-standby, archiving 4 months of wal files,
 and even a nightly pg_dump all. 50G database.  Trying to update or delete a
 row in a small (21 row, but heavily used table) would lock up completely.
 Never finish. Removed all clients, restarted the db instance, no joy. Check
 pg_stat_activity, and nothing that wasn't idle run the delete, locked
 up.

I'm betting one of those prepared transactions had updated or deleted
this row, and thus held a row lock on it.  (Come to think of it, a
SELECT FOR UPDATE/SHARE might have been enough.)  Did you try committing
or rolling back those xacts?

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] Database corruption event, unlockable rows, possibly bogus virtual xids? Invalid backend server xid

2013-02-21 Thread Ned Wolpert

Tom and Kevin-

  There were two entries in pg_prepared_xacts. In the test-bed, executing
the 'ROLLBACK PREPARED' on both allowed the system to continue processing.
All locks I saw in 'pg_locks' where the virtualtransaction started with the
'-1/' were also gone. That was indeed the issue. More importantly to me,
there was no issue likely leftover during our 9.1.4-9.1.6 upgrade we did;
just a 'flaky transaction manager' as you suspected.

  Thanks to both of you for help in tracking this down.

P.S. Kevin, We also do tar archives of the data directories nightly to
accompany the wal files we store, using pg_start_backup/pg_stop_backup. :-)
 Full restores are tested monthly.


On Thu, Feb 21, 2013 at 2:06 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 Ned Wolpert ned.wolp...@imemories.com writes:
Event: Running 9.1.6 with hot-standby, archiving 4 months of wal files,
  and even a nightly pg_dump all. 50G database.  Trying to update or
 delete a
  row in a small (21 row, but heavily used table) would lock up completely.
  Never finish. Removed all clients, restarted the db instance, no joy.
 Check
  pg_stat_activity, and nothing that wasn't idle run the delete, locked
  up.

 I'm betting one of those prepared transactions had updated or deleted
 this row, and thus held a row lock on it.  (Come to think of it, a
 SELECT FOR UPDATE/SHARE might have been enough.)  Did you try committing
 or rolling back those xacts?

 regards, tom lane




-- 
Virtually, Ned Wolpert

Settle thy studies, Faustus, and begin...   --Marlowe

Re: [GENERAL] Need help extripating plpgsql


On Thu, February 21, 2013 16:02, Adrian Klaver wrote:
 On 02/21/2013 12:14 PM, James B. Byrne wrote:


 The current arrangement is not really satisfactory as it requires
 either separate template databases for each userid granted the
 DBCREATE role or the superuser role has to be granted in place of
 the DBCREATE role.



 That is sort of the point of the template system, different templates
 for different situations.


Creating a new database from the system provided standard templates is
not what I would consider a different situation requiring a
specialized template for each and every user granted the DBCREATE
role.  Requiring that seems to me to be busywork and a complete waste
of DBA resources.

If all the elements contained in the standard templates had their
ownerships changed to that of the owner of the new database then my
problem would never have arisen.  I do not understand why this is not
the case.  Is there a reason why this is so?


-- 
***  E-Mail is NOT a SECURE channel  ***
James B. Byrnemailto:byrn...@harte-lyne.ca
Harte  Lyne Limited  http://www.harte-lyne.ca
9 Brockley Drive  vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada  L8E 3C3



-- 
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 extripating plpgsql


On 02/21/2013 03:18 PM, James B. Byrne wrote:


On Thu, February 21, 2013 16:02, Adrian Klaver wrote:

On 02/21/2013 12:14 PM, James B. Byrne wrote:




The current arrangement is not really satisfactory as it requires
either separate template databases for each userid granted the
DBCREATE role or the superuser role has to be granted in place of
the DBCREATE role.




That is sort of the point of the template system, different templates
for different situations.



Creating a new database from the system provided standard templates is
not what I would consider a different situation requiring a
specialized template for each and every user granted the DBCREATE
role.  Requiring that seems to me to be busywork and a complete waste
of DBA resources.

If all the elements contained in the standard templates had their
ownerships changed to that of the owner of the new database then my
problem would never have arisen.  I do not understand why this is not
the case.  Is there a reason why this is so?


Hmm, you might be on to something:

I changed owner in template1 to me:

p_test=# \c template1
You are now connected to database template1 as user postgres.
template1=# \dL
   List of languages
   Name|  Owner   | Trusted | Description
---+--+-+--
 plpgsql   | aklaver  | t   | PL/pgSQL procedural language
 plpythonu | postgres | f   |
(2 rows)

Create new database as me:

template1=# \c - aklaver
You are now connected to database template1 as user aklaver.
template1= create database p_test template=template1 owner=aklaver;
CREATE DATABASE
template1= \c p_test
You are now connected to database p_test as user aklaver.

In new database language is owned by me.

p_test= \dL
   List of languages
   Name|  Owner   | Trusted | Description
---+--+-+--
 plpgsql   | aklaver  | t   | PL/pgSQL procedural language
 plpythonu | postgres | f   |
(2 rows)


Dump the database:

aklaver@panda:~ /usr/local/pgsql92/bin/pg_dump p_test -C -U aklaver -p 
5442 -f p_test.sql



Dropped the database:

postgres= drop database p_test ;
DROP DATABASE


Restored it:

aklaver@panda:~ /usr/local/pgsql92/bin/psql -d postgres -U aklaver -p 
5442 -f p_test.sql

SET
SET
SET
SET
SET
CREATE DATABASE
ALTER DATABASE
You are now connected to database p_test as user aklaver.
SET
SET
SET
SET
SET
CREATE EXTENSION
psql:p_test.sql:39: ERROR:  must be owner of extension plpgsql



Now plpgsql is back to being owned by postgres:


postgres= \c p_test
You are now connected to database p_test as user aklaver.
p_test= \dL
  List of languages
  Name   |  Owner   | Trusted | Description
-+--+-+--
 plpgsql | postgres | t   | PL/pgSQL procedural language
(1 row)


The issue seems to be, from the p_test.sql file:

CREATE DATABASE p_test WITH TEMPLATE = template0 ENCODING = 'UTF8' 
LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';



It is creating the database using template0 instead for the template 
specified in the CREATE DATABASE  run from psql.








--
Adrian Klaver
adrian.kla...@gmail.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] Need help extripating plpgsql