Re: [GENERAL] Query not using the indexes properly.
On Thu, Oct 1, 2009 at 10:21 PM, Tim Uckun wrote: > > Interesting. I would have thought the order of the fields would not > matter. I don't have to rewrite the query do I? > > No. For multi-column indices, however, postgres can, starting at the leftmost in the index, use as many columns as match equality comparisons plus one column using an inequality comparison. >From our fine manual, section 11.3: "A multicolumn B-tree index can be used with query conditions that involve any subset of the index's columns, but the index is most efficient when there are constraints on the leading (leftmost) columns. The exact rule is that equality constraints on leading columns, plus any inequality constraints on the first column that does not have an equality constraint, will be used to limit the portion of the index that is scanned. Constraints on columns to the right of these columns are checked in the index, so they save visits to the table proper, but they do not reduce the portion of the index that has to be scanned. For example, given an index on (a, b, c) and a query condition WHERE a = 5 AND b >= 42 AND c < 77, the index would have to be scanned from the first entry with a = 5 and b = 42 up through the last entry with a = 5. Index entries with c >= 77 would be skipped, but they'd still have to be scanned through. This index could in principle be used for queries that have constraints on b and/or c with no constraint on a — but the entire index would have to be scanned, so in most cases the planner would prefer a sequential table scan over using the index." -- - David T. Wilson david.t.wil...@gmail.com
Re: [GENERAL] Query not using the indexes properly.
> > If you try the multi-column index (which is a good idea), be sure that "id" > is the last of the three columns, since that's the column on which you have > an inequality test rather than an equality test; eg, > (company_id,source_model_name,id). > Interesting. I would have thought the order of the fields would not matter. I don't have to rewrite the query do I? -- 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] Query not using the indexes properly.
> It probably thinks the id check is going to be better to limit the result > set. > > How many records are there for id > 1935759 ? About 40 million or so. > vs > How many records for company_id = 4 and source_model_name = > 'CommissionedVisit' ? > > If this is a common query you could probably do a multi-column index on all > 3 columns (id, company_id, source_model_name) - but if company_id and > source_model_name have a low number of distinct values, then it's not going > to help. Both of those will indeed have a pretty low number of distinct values. Looks like I need to figure out something else. -- 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] Query not using the indexes properly.
On Thu, Oct 1, 2009 at 10:04 PM, Chris wrote: > Tim Uckun wrote: > > If this is a common query you could probably do a multi-column index on all > 3 columns (id, company_id, source_model_name) - but if company_id and > source_model_name have a low number of distinct values, then it's not going > to help. > If you try the multi-column index (which is a good idea), be sure that "id" is the last of the three columns, since that's the column on which you have an inequality test rather than an equality test; eg, (company_id,source_model_name,id). -- - David T. Wilson david.t.wil...@gmail.com
Re: [GENERAL] Query not using the indexes properly.
Tim Uckun wrote: I have a pretty simple query on a pretty simple table with about 60 million records in it. This is the query. SELECT * FROM "changes" WHERE (id > 1935759 and company_id = 4 and source_model_name = 'CommissionedVisit') ORDER BY id ASC LIMIT 1 The id field is the primary key. The other fields are indexed (company_id and source_model_name). This query takes about 30 seconds to run on a pretty beefy machine. Here is the explain. "Limit (cost=0.00..7.46 rows=1 width=45) (actual time=28799.712..28799.712 rows=0 loops=1)" " -> Index Scan using changes_pkey on changes (cost=0.00..2331939.52 rows=312519 width=45) (actual time=28799.710..28799.710 rows=0 loops=1)" "Index Cond: (id > 1935759)" "Filter: ((company_id = 4) AND ((source_model_name)::text = 'CommissionedVisit'::text))" "Total runtime: 28799.749 ms" It seem to me that it's ignoring the indexes on the text fields. Is that right? It probably thinks the id check is going to be better to limit the result set. How many records are there for id > 1935759 ? vs How many records for company_id = 4 and source_model_name = 'CommissionedVisit' ? If this is a common query you could probably do a multi-column index on all 3 columns (id, company_id, source_model_name) - but if company_id and source_model_name have a low number of distinct values, then it's not going to help. -- Postgresql & php tutorials http://www.designmagick.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Query not using the indexes properly.
I have a pretty simple query on a pretty simple table with about 60 million records in it. This is the query. SELECT * FROM "changes" WHERE (id > 1935759 and company_id = 4 and source_model_name = 'CommissionedVisit') ORDER BY id ASC LIMIT 1 The id field is the primary key. The other fields are indexed (company_id and source_model_name). This query takes about 30 seconds to run on a pretty beefy machine. Here is the explain. "Limit (cost=0.00..7.46 rows=1 width=45) (actual time=28799.712..28799.712 rows=0 loops=1)" " -> Index Scan using changes_pkey on changes (cost=0.00..2331939.52 rows=312519 width=45) (actual time=28799.710..28799.710 rows=0 loops=1)" "Index Cond: (id > 1935759)" "Filter: ((company_id = 4) AND ((source_model_name)::text = 'CommissionedVisit'::text))" "Total runtime: 28799.749 ms" It seem to me that it's ignoring the indexes on the text fields. Is that right? I did a vacuum analyze on the table but that didn't seem to help at all. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Programming interfaces when using MD5 authentication
Hi,
I apologise in advance if this is considered the wrong list to post
onto. I couldn't find specific details for joining a DBD::Pg style
mailing list so I'm hoping this is something that's relatively well
known about by general PostgreSQL developers anyway.
Using Perl to program interfaces to PostgreSQL, and had previously
misunderstood how md5 password authentication worked, so I'm now re-
doing it, but struggling to find out how DBD::Pg might be used to
actually authenticate by passing an md5 of the password instead of the
real thing.
I understand from various reading that the md5 should be a double-
process consisting of:
phase1 = md5(password username)
password_to_use = md5(phase1 salt)
What I don't understand is how to "extract" the salt required to
complete the second phase.
Effectively what I'm hoping for is to be able to do something along
the lines of:
---
#!/usr/local/bin/perl -w
use strict;
use DBI;
use Digest::MD5 qw(md5_hex);
use DBD::Pg;
my $user = "currentuser";
my $pass = md5_hex("supersecretpassword" . $user);
my $dbh = DBI->connect("dbi:Pg:dbname=monitoring","$user",$pass,
{ PrintError => 1 });
if (defined($dbh)) {
$dbh->disconnect();
print "Successful\n";
} else {
print "Failed!!\n";
}
---
In the above, if I prepend "md5" to the $pass variable I obviously get
what exactly matches the content of the pg_shadow table entry for the
given user ... however, either way the connection isn't successful
because (from what I've been able to discern) I actually need to submit:
md5 md5($pass salt)
Can DBD::Pg be used for these connections? If anyone has experience in
this I'd much appreciate your thoughts or suggestions. (I realise the
"connect" function is from DBI, but it seems to me that the use of the
salt required to properly authenticate will be specific somehow to
DBD::Pg usage.)
Cheers,
Preston.
--
Preston de Guise
http://www.enterprisesystemsbackup.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] Weird behavior with "sensitive" cursors.
This one really works and includes a basic test case. You were right
that the extra Register was bogus :-( I had to expose CopySnapshot,
which I still don't like but ... (I could have added an extra
Unregister somewhere during portal close, but it would have meant making
everything messier).
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
*** src/backend/commands/portalcmds.c 11 Jun 2009 14:48:56 - 1.79
--- src/backend/commands/portalcmds.c 1 Oct 2009 23:24:33 -
***
*** 47,52
--- 47,53
DeclareCursorStmt *cstmt = (DeclareCursorStmt *) stmt->utilityStmt;
Portal portal;
MemoryContext oldContext;
+ Snapshot snapshot;
if (cstmt == NULL || !IsA(cstmt, DeclareCursorStmt))
elog(ERROR, "PerformCursorOpen called for non-cursor query");
***
*** 119,127
}
/*
* Start execution, inserting parameters if any.
*/
! PortalStart(portal, params, GetActiveSnapshot());
Assert(portal->strategy == PORTAL_ONE_SELECT);
--- 120,136
}
/*
+ * Set up snapshot for portal. Note that we need a fresh, independent copy
+ * of the snapshot because we don't want it to be modified by future
+ * CommandCounterIncrement calls. We do not register it, because
+ * portalmem.c will take care of that internally.
+ */
+ snapshot = CopySnapshot(GetActiveSnapshot());
+
+ /*
* Start execution, inserting parameters if any.
*/
! PortalStart(portal, params, snapshot);
Assert(portal->strategy == PORTAL_ONE_SELECT);
*** src/backend/executor/spi.c 11 Jun 2009 14:48:57 - 1.208
--- src/backend/executor/spi.c 1 Oct 2009 23:26:31 -
***
*** 1211,1220
}
}
! /*
! * Set up the snapshot to use. (PortalStart will do PushActiveSnapshot,
! * so we skip that here.)
! */
if (read_only)
snapshot = GetActiveSnapshot();
else
--- 1211,1217
}
}
! /* Set up the snapshot to use. */
if (read_only)
snapshot = GetActiveSnapshot();
else
*** src/backend/utils/time/snapmgr.c 11 Jun 2009 14:49:06 - 1.10
--- src/backend/utils/time/snapmgr.c 1 Oct 2009 23:29:05 -
***
*** 104,110
static bool registered_serializable = false;
- static Snapshot CopySnapshot(Snapshot snapshot);
static void FreeSnapshot(Snapshot snapshot);
static void SnapshotResetXmin(void);
--- 104,109
***
*** 192,198
* The copy is palloc'd in TopTransactionContext and has initial refcounts set
* to 0. The returned snapshot has the copied flag set.
*/
! static Snapshot
CopySnapshot(Snapshot snapshot)
{
Snapshot newsnap;
--- 191,197
* The copy is palloc'd in TopTransactionContext and has initial refcounts set
* to 0. The returned snapshot has the copied flag set.
*/
! Snapshot
CopySnapshot(Snapshot snapshot)
{
Snapshot newsnap;
*** src/include/utils/snapmgr.h 11 Jun 2009 14:49:13 - 1.5
--- src/include/utils/snapmgr.h 1 Oct 2009 23:28:19 -
***
*** 26,31
--- 26,32
extern Snapshot GetTransactionSnapshot(void);
extern Snapshot GetLatestSnapshot(void);
extern void SnapshotSetCommandId(CommandId curcid);
+ extern Snapshot CopySnapshot(Snapshot snapshot);
extern void PushActiveSnapshot(Snapshot snapshot);
extern void PushUpdatedSnapshot(Snapshot snapshot);
*** src/test/regress/expected/portals.out 27 Jan 2009 12:40:15 - 1.21
--- src/test/regress/expected/portals.out 1 Oct 2009 23:44:17 -
***
*** 1242,1244
--- 1242,1259
DELETE FROM ucview WHERE CURRENT OF c1; -- fail, views not supported
ERROR: WHERE CURRENT OF on a view is not implemented
ROLLBACK;
+ -- Make sure snapshot management works okay, per bug report in
+ -- 235395b90909301035v7228ce63q392931f15aa74...@mail.gmail.com
+ BEGIN;
+ SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+ CREATE TABLE cursor (a int);
+ INSERT INTO cursor VALUES (1);
+ DECLARE c1 NO SCROLL CURSOR FOR SELECT * FROM cursor FOR UPDATE;
+ UPDATE cursor SET a = 2;
+ FETCH ALL FROM c1;
+ a
+ ---
+ (0 rows)
+
+ COMMIT;
+ DROP TABLE cursor;
*** src/test/regress/sql/portals.sql 16 Nov 2008 17:34:28 - 1.16
--- src/test/regress/sql/portals.sql 1 Oct 2009 23:39:08 -
***
*** 458,460
--- 458,472
FETCH FROM c1;
DELETE FROM ucview WHERE CURRENT OF c1; -- fail, views not supported
ROLLBACK;
+
+ -- Make sure snapshot management works okay, per bug report in
+ -- 235395b90909301035v7228ce63q392931f15aa74...@mail.gmail.com
+ BEGIN;
+ SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+ CREATE TABLE cursor (a int);
+ INSERT INTO cursor VALUES (1);
+ DECLARE c1 NO SCROLL CURSOR FOR SELECT * FROM cursor FOR UPDATE;
+ UPDATE cursor SET a = 2;
+ FETCH ALL FROM c1;
+ COMMIT;
+ DROP TABLE cursor;
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to
Re: [GENERAL] Procedure for feature requests?
Tim Landscheidt wrote: > Hi, > > suppose I thought that PostgreSQL would benefit greatly from > a "generate_series(DATE, DATE[, INT]) RETURNS DATE" function > - where do I suggest such a thing? Here on -general? On > -hackers? Directly edit > http://wiki.postgresql.org/wiki/Todo>? I think direct edition of Todo is discouraged, particularly for outsiders. Suppose we decided that we didn't want to implement your suggestion for whatever reason? We get lots of people coming out of the blue with a patch to implement something they found on Todo, only to figure out that we didn't want the idea implemented in the first place. Surely we don't want to turn people away from development just because Todo is not well managed. > Suppose the feature request was not a trivial one, but > maybe a "DEPENDS ON " clause for "CREATE FUNCTION" > to allow PostgreSQL to deny requests to drop a table/view/ > function that is needed by a function - where would I pro- > pose that? On -hackers, just like any other feature request, trivial or not. -- 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] Procedure for feature requests?
Hi, suppose I thought that PostgreSQL would benefit greatly from a "generate_series(DATE, DATE[, INT]) RETURNS DATE" function - where do I suggest such a thing? Here on -general? On -hackers? Directly edit http://wiki.postgresql.org/wiki/Todo>? Suppose the feature request was not a trivial one, but maybe a "DEPENDS ON " clause for "CREATE FUNCTION" to allow PostgreSQL to deny requests to drop a table/view/ function that is needed by a function - where would I pro- pose that? TIA, Tim -- 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] Weird behavior with "sensitive" cursors.
Tom Lane escribió: > Alvaro Herrera writes: > > Tom Lane escribi�: > >> I don't think that testing rowMarks is the right thing at all here. > >> That tells you whether it's a SELECT FOR UPDATE, but actually we > >> want any cursor (and only cursors) to have a private snapshot. > > > The attached patch implements this. I intend to apply to 8.4 and HEAD > > shortly. > > Looks sane. Can we add a short regression test sequence that checks > for this? Something is wrong with the patch :-( I'm getting WARNING: Snapshot reference leak: Snapshot 0x1be5840 still referenced with a simple test case. Still investigating. -- 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
Re: [GENERAL] Vacuumdb Fails: Huge Tuple
APseudoUtopia writes: >> Here's what happened: >> >> $ vacuumdb --all --full --analyze --no-password >> vacuumdb: vacuuming database "postgres" >> vacuumdb: vacuuming database "web_main" >> vacuumdb: vacuuming of database "web_main" failed: ERROR: Â huge tuple > PostgreSQL 8.4.0 on i386-portbld-freebsd7.2, compiled by GCC cc (GCC) > 4.2.1 20070719 [FreeBSD], 32-bit This is evidently coming out of ginHeapTupleFastCollect because it's formed a GIN tuple that is too large (either too long a word, or too many postings, or both). I'd say that this represents a serious degradation in usability from pre-8.4 releases: before, you would have gotten the error upon attempting to insert the table row that triggers the problem. Now, with the "fast insert" stuff, you don't find out until VACUUM fails, and you have no idea where the bad data is. Not cool. Oleg, Teodor, what can we do about this? Can we split an oversize tuple into multiple entries? Can we apply suitable size checks before instead of after the fast-insert queue? 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] Vacuumdb Fails: Huge Tuple
Scott Marlowe escribió: > Wow, that's pretty slow. I'd assumed it was a semi-automated process > and the new version would be out now, 3 weeks later. At least look > through the release notes to see if any mention is made of this bug > being fixed in 8.4.1 I guess. Both files on which that error message appears are still at the same versions there were at when 8.4.0 was released, so I doubt the bug has been fixed. -- 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] Vacuumdb Fails: Huge Tuple
On Thu, Oct 1, 2009 at 2:27 PM, APseudoUtopia wrote: > On Thu, Oct 1, 2009 at 4:21 PM, Scott Marlowe wrote: >> On Thu, Oct 1, 2009 at 1:12 PM, APseudoUtopia >> wrote: >> >>> Sorry, I failed to mention: >>> >>> PostgreSQL 8.4.0 on i386-portbld-freebsd7.2, compiled by GCC cc (GCC) >>> 4.2.1 20070719 [FreeBSD], 32-bit >> >> Have you tried updating to 8.4.1 to see if that fixes the problem? >> > > I have not. The FreeBSD port for postgres has not yet been updated to > 8.4.1, so I'm unable to upgrade (I'd strongly prefer to use the ports > system rather than manually compiling). > > As soon as the port is updated, I'm going to upgrade. Wow, that's pretty slow. I'd assumed it was a semi-automated process and the new version would be out now, 3 weeks later. At least look through the release notes to see if any mention is made of this bug being fixed in 8.4.1 I guess. -- 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] Weird behavior with "sensitive" cursors.
Alvaro Herrera writes: > Tom Lane escribió: >> I don't think that testing rowMarks is the right thing at all here. >> That tells you whether it's a SELECT FOR UPDATE, but actually we >> want any cursor (and only cursors) to have a private snapshot. > The attached patch implements this. I intend to apply to 8.4 and HEAD > shortly. Looks sane. Can we add a short regression test sequence that checks for this? 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] Vacuumdb Fails: Huge Tuple
On Thu, Oct 1, 2009 at 4:21 PM, Scott Marlowe wrote: > On Thu, Oct 1, 2009 at 1:12 PM, APseudoUtopia wrote: > >> Sorry, I failed to mention: >> >> PostgreSQL 8.4.0 on i386-portbld-freebsd7.2, compiled by GCC cc (GCC) >> 4.2.1 20070719 [FreeBSD], 32-bit > > Have you tried updating to 8.4.1 to see if that fixes the problem? > I have not. The FreeBSD port for postgres has not yet been updated to 8.4.1, so I'm unable to upgrade (I'd strongly prefer to use the ports system rather than manually compiling). As soon as the port is updated, I'm going to upgrade. -- 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] Vacuumdb Fails: Huge Tuple
On Thu, Oct 1, 2009 at 1:12 PM, APseudoUtopia wrote: > Sorry, I failed to mention: > > PostgreSQL 8.4.0 on i386-portbld-freebsd7.2, compiled by GCC cc (GCC) > 4.2.1 20070719 [FreeBSD], 32-bit Have you tried updating to 8.4.1 to see if that fixes the problem? -- 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] Weird behavior with "sensitive" cursors.
Tom Lane escribió:
> Alvaro Herrera writes:
> > Tom Lane escribió:
> >> Well, the first problem is that 8.4 is failing to duplicate the
> >> historical behavior.
>
> > Oh! That's easy.
>
> I don't think that testing rowMarks is the right thing at all here.
> That tells you whether it's a SELECT FOR UPDATE, but actually we
> want any cursor (and only cursors) to have a private snapshot.
The attached patch implements this. I intend to apply to 8.4 and HEAD
shortly.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Index: src/backend/commands/portalcmds.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/commands/portalcmds.c,v
retrieving revision 1.79
diff -c -p -r1.79 portalcmds.c
*** src/backend/commands/portalcmds.c 11 Jun 2009 14:48:56 - 1.79
--- src/backend/commands/portalcmds.c 1 Oct 2009 19:35:15 -
*** PerformCursorOpen(PlannedStmt *stmt, Par
*** 47,52
--- 47,53
DeclareCursorStmt *cstmt = (DeclareCursorStmt *) stmt->utilityStmt;
Portal portal;
MemoryContext oldContext;
+ Snapshot snapshot;
if (cstmt == NULL || !IsA(cstmt, DeclareCursorStmt))
elog(ERROR, "PerformCursorOpen called for non-cursor query");
*** PerformCursorOpen(PlannedStmt *stmt, Par
*** 119,127
}
/*
* Start execution, inserting parameters if any.
*/
! PortalStart(portal, params, GetActiveSnapshot());
Assert(portal->strategy == PORTAL_ONE_SELECT);
--- 120,136
}
/*
+ * Set up snapshot for portal. Note that we need a fresh, independent copy
+ * of the snapshot because we don't want it to be modified by future
+ * CommandCounterIncrement calls.
+ */
+ snapshot = RegisterCopiedSnapshot(GetActiveSnapshot(),
+ portal->resowner);
+
+ /*
* Start execution, inserting parameters if any.
*/
! PortalStart(portal, params, snapshot);
Assert(portal->strategy == PORTAL_ONE_SELECT);
Index: src/backend/utils/time/snapmgr.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/utils/time/snapmgr.c,v
retrieving revision 1.10
diff -c -p -r1.10 snapmgr.c
*** src/backend/utils/time/snapmgr.c 11 Jun 2009 14:49:06 - 1.10
--- src/backend/utils/time/snapmgr.c 1 Oct 2009 17:24:40 -
*** RegisterSnapshotOnOwner(Snapshot snapsho
*** 385,390
--- 385,403
}
/*
+ * As above, but create a new, independeny copy of the snapshot.
+ */
+ Snapshot
+ RegisterCopiedSnapshot(Snapshot snapshot, ResourceOwner owner)
+ {
+ if (snapshot == InvalidSnapshot)
+ return InvalidSnapshot;
+
+ snapshot = CopySnapshot(snapshot);
+ return RegisterSnapshotOnOwner(snapshot, owner);
+ }
+
+ /*
* UnregisterSnapshot
*
* Decrement the reference count of a snapshot, remove the corresponding
Index: src/include/utils/snapmgr.h
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/include/utils/snapmgr.h,v
retrieving revision 1.5
diff -c -p -r1.5 snapmgr.h
*** src/include/utils/snapmgr.h 11 Jun 2009 14:49:13 - 1.5
--- src/include/utils/snapmgr.h 1 Oct 2009 17:24:40 -
*** extern bool ActiveSnapshotSet(void);
*** 36,41
--- 36,42
extern Snapshot RegisterSnapshot(Snapshot snapshot);
extern void UnregisterSnapshot(Snapshot snapshot);
extern Snapshot RegisterSnapshotOnOwner(Snapshot snapshot, ResourceOwner owner);
+ extern Snapshot RegisterCopiedSnapshot(Snapshot snapshot, ResourceOwner owner);
extern void UnregisterSnapshotFromOwner(Snapshot snapshot, ResourceOwner owner);
extern void AtSubCommit_Snapshot(int level);
--
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] Vacuumdb Fails: Huge Tuple
On Thu, Oct 1, 2009 at 3:10 PM, APseudoUtopia wrote: > Hey list, > > After some downtime of my site while completing rigorous database > maintenance, I wanted to make sure all the databases were fully > vacuumed and analyzed. I do run autovacuum, but since I made several > significant changes, I wanted to force a vacuum before I brought my > site back online. > > Here's what happened: > > $ vacuumdb --all --full --analyze --no-password > vacuumdb: vacuuming database "postgres" > vacuumdb: vacuuming database "web_main" > vacuumdb: vacuuming of database "web_main" failed: ERROR: huge tuple > > I was told on IRC that this may be related to a GIN index. I do have > several GIN indexes used for full-text searching. I tried googling, > but nothing much came up. > > Thanks for the help. > Sorry, I failed to mention: PostgreSQL 8.4.0 on i386-portbld-freebsd7.2, compiled by GCC cc (GCC) 4.2.1 20070719 [FreeBSD], 32-bit -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Vacuumdb Fails: Huge Tuple
Hey list, After some downtime of my site while completing rigorous database maintenance, I wanted to make sure all the databases were fully vacuumed and analyzed. I do run autovacuum, but since I made several significant changes, I wanted to force a vacuum before I brought my site back online. Here's what happened: $ vacuumdb --all --full --analyze --no-password vacuumdb: vacuuming database "postgres" vacuumdb: vacuuming database "web_main" vacuumdb: vacuuming of database "web_main" failed: ERROR: huge tuple I was told on IRC that this may be related to a GIN index. I do have several GIN indexes used for full-text searching. I tried googling, but nothing much came up. Thanks for the help. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Pg West in two weeks!
PostgreSQL Conference West is set to hit in two weeks! Running from October 16th-18th a Central Seattle Community College, this West is set to be the largest West Coast PostgreSQL conference to date. Our list of talks is up: http://www.postgresqlconference.org/2009/west/talks Our tentative schedule is up: http://www.postgresqlconference.org/2009/west/schedule And our registration is up: http://www.postgresql.us/purchase Remember folks, all proceeds are a donation to United States (PgUS) PostgreSQL. A United States 501c3. Joshua D. Drake -- -- 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] error message on install [ REPOST from pgsql-novice ]
On 10/01/2009 07:00 PM, Ounce Snow wrote: Hi, this may be a FAQ but I did not see it listed there: when I run the install (Intel Mac) I get a popup saying problem running post install step Installation may not complete correctly The database cluster initialisation failed Check the logs(/tmp/install-postgresql.log). You can also post the error part of the log. what have I missed please? Greg -- Regards, Sachin Srivastava www.enterprisedb.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] I can not drop a user/role because an object depent on it.
- "Ricky Tompu Breaky" wrote: > On Wed, 30 Sep 2009 11:38:19 -0700 > Adrian Klaver wrote: > > > On Wednesday 30 September 2009 10:43:35 am Ricky Tompu Breaky > wrote: > > > Dear my friends > > > > > > I can not drop a user because another object need it. How can I > know > > > which object need it? I really want to drop everything inside my > > > PostgreSQL, to reset my installation really from beginning. > > > > > > postgres=# drop user ivia; > > > FEHLER: kann Rolle »ivia« nicht löschen, weil andere Objekte > davon > > > abhängen DETAIL: Eigentümer von Datenbank iVia > > > 7 Objekte in Datenbank iVia-->mytranslation: Error: can not > delete > > > Role »ivia«, because another object depend on it DETAIL: owner of > > > Database iVia. > > > postgres=# > > > > > > I've dropped the database iVia. > > > > > > Thank you very much in advance. > > > > To clarify did you try to DROP USER ivia before or after you > dropped > > the database iVia ? > > > > RB>It's solved. Thanks for your reply. You're correct, I wanted to > drop > RB>the user after I dropped the database. Now I know from you that in > RB>PostgreSQL, I have to drop the user first before dropping the > RB>database. Not to belabor the point, but you have it backwards. The database needs to be dropped first, then the role/user. The database is owned by the role/user. Adrian Klaver akla...@comcast.net -- 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] Weird behavior with "sensitive" cursors.
Tom Lane escribió: > Alvaro Herrera writes: > > Tom Lane escribió: > >> Well, the first problem is that 8.4 is failing to duplicate the > >> historical behavior. > > > Oh! That's easy. > > I don't think that testing rowMarks is the right thing at all here. > That tells you whether it's a SELECT FOR UPDATE, but actually we > want any cursor (and only cursors) to have a private snapshot. Hmm, okay. > Also, do we really need the Register bit? Won't the portal register > its use of the snapshot anyway (or if it doesn't, isn't that a bug)? Perhaps, but registering it a second time does not harm, and I think it's cleaner to expose the new RegisterCopiedSnapshot function than bare CopySnapshot. In PortalStart there's something I'd like to clean up in HEAD, which is that we're setting up the snapshot as Active only to be able to do GetActive to pass it down to CreateQueryDesc. That's silly -- we should just get a local snap to pass down; no need to push, get, pop. -- 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] error message on install [ REPOST from pgsql-novice ]
Hi, this may be a FAQ but I did not see it listed there: when I run the install (Intel Mac) I get a popup saying problem running post install step Installation may not complete correctly The database cluster initialisation failed what have I missed please? Greg
[GENERAL] Time Management - Training Seminar in Cape Town
A training seminar that will put more time back in your life. A training provider registered with Services SETA Presented by South Africa's leading Speakers Dr Brian Jude and Associates present MORE HOURS IN YOUR DAY (Time management) A Dynamic Morning Seminar Time is a unique resource. There isn't any more of it. Each of us already has all the time there is, yet few of us have enough.This course will not give you more time, but will show you how to make better use of the time you have. Presented by Dr Brian Jude Time management is the key to improved productivity, with less stress, and MORE leisure time. This programme will show you how to achieve this. Tuesday 3rd November 2009 Venue: The River Club Conference Centre, Liesbeek Parkway, Observatory - Cape Town Time: Registration 08:30 Start 09:00 Conclude 12:30 Cost: Only R595-00 per delegate per course inclusive of VAT. Registered with the Services SETA. TARGET GROUP. All staff. OBJECTIVES. To learn to make better use of time. To be able to achieve more, in less time. To develop skills that will make us, and the people around us more productive. To improve overall effectiveness and productivity. METHODOLOGY. All training is designed to achieve maximum group attention. Fast paced and entertaining, it allows the delegates to take away real practical skills, rather than just theoretical knowledge. TIMING. One morning. 8:30 am to 12:00 pm. COURSE CONTENT. Time - What it is. How to use it and not abuse it. How well do you manage time.? How to set goals and work with a "To Do" list. Setting priorities. The difference between urgent and important work. The 80/20 rule. Speed reading. Speed sleeping. Paper management. Desk management. Avoiding the pitfalls of poor delegation. How to handle procrastination. How to handle correspondence. How to handle telephones. How to handle visitors. How to handle meetings. End. Learn to take charge of your time and get more done, with less stress. This programme shows how. Book NOW, space is limited E-mail: i...@mastersuccess.co.za "mailto:i...@mastersuccess.co.za"; Tel: 011 485 2150 Fax: 011 640 4916 P O Box 29176, Sandringham, Johannesburg, 2131 The information we will need is: Company name, your name, postal address, fax and phone numbers and number of delegates attending. As a valued client or potential client, we do not want to send you e-mails that do not add value to you or your business. If you do not want to continue getting our newsletters, product advices, special offers and information sheets, please go to the "unsubscribe" link at the end of this mail and you will be deleted from our lists. Alternatively, reply to this e-mail, putting "unsubscribe" in the subject line. Many thanks. Unsubscribe http://www.superlearning.co.za/cgi-bin/uls/uls.cgi?ulsRemove=16-52664=920 Update Profile http://www.superlearning.co.za/cgi-bin/uls/uls_mem.cgi?login=Yes&action=process&subscriberid=16-52664&email=pgsql-gene...@postgresql.org
Re: [GENERAL] Collation in ORDER BY not lexicographical
Thank you all very much for your help. Maximilian, we simplified your replacing code: replace(replace(replace(replace(replace(replace ($1,'Ä','A'),'Ö','O'),'Ü','U' ),'ä','a'),'ö','o'),'ü','u'); to this: translate(upper($1),'ÄÖÜ','AOU') Paul Am 29.09.2009 um 14:36 schrieb Maximilian Tyrtania: am 29.09.2009 11:21 Uhr schrieb Scott Marlowe unter scott.marl...@gmail.com : On Tue, Sep 29, 2009 at 2:52 AM, Paul Gaspar > wrote: Hi! We have big problems with collation in ORDER BY, which happens in binary order, not alphabetic (lexicographical), like:. PG is running on Mac OS X 10.5 and 10.6 Intel. I seem to recall there were some problem with Mac locales at some point being broken. Could be you're running into that issue. Yep, i ran into this as well. Here is my workaround: Create a function like this: CREATE OR REPLACE FUNCTION f_getorderbyfriendlyversion(texttoconvert text) RETURNS text AS $BODY$ select replace(replace(replace(replace(replace(replace ($1,'Ä','A'),'Ö','O'),'Ü','U' ),'ä','a'),'ö','o'),'ü','u'); $BODY$ LANGUAGE 'sql' IMMUTABLE STRICT COST 100; ALTER FUNCTION f_getorderbyfriendlyversion(text) OWNER TO postgres; Then create an index like this: create index idx_personen_nachname_orderByFriendly on personen (f_getorderbyfriendlyversion(nachname)) Now you can do: select * from personen order by f_getorderbyfriendlyversion (p.nachname) Seems pretty fast. Best, Maximilian Tyrtania -- 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] I can not drop a user/role because an object depent on it.
On Wed, 30 Sep 2009 11:38:19 -0700 Adrian Klaver wrote: > On Wednesday 30 September 2009 10:43:35 am Ricky Tompu Breaky wrote: > > Dear my friends > > > > I can not drop a user because another object need it. How can I know > > which object need it? I really want to drop everything inside my > > PostgreSQL, to reset my installation really from beginning. > > > > postgres=# drop user ivia; > > FEHLER: kann Rolle »ivia« nicht löschen, weil andere Objekte davon > > abhängen DETAIL: Eigentümer von Datenbank iVia > > 7 Objekte in Datenbank iVia-->mytranslation: Error: can not delete > > Role »ivia«, because another object depend on it DETAIL: owner of > > Database iVia. > > postgres=# > > > > I've dropped the database iVia. > > > > Thank you very much in advance. > > To clarify did you try to DROP USER ivia before or after you dropped > the database iVia ? > RB>It's solved. Thanks for your reply. You're correct, I wanted to drop RB>the user after I dropped the database. Now I know from you that in RB>PostgreSQL, I have to drop the user first before dropping the RB>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] pg_dump and check-constraints
"A. Kretschmer" writes: > test=# create function check_b() returns bool as $$ declare s int; begin > select into s sum(i) from b; if s > 3 then return true; else return false; > end if; end;$$ language plpgsql; > test=*# create table a (i int check(check_b())); This is unsupported, and will fail in *many* situations not only pg_dump. A check constraint is only expected to examine the current row of its table. 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] pg_dump and check-constraints
Hi, For instance, i have such a database: (it is just a silly example) test=# create function check_b() returns bool as $$ declare s int; begin select into s sum(i) from b; if s > 3 then return true; else return false; end if; end;$$ language plpgsql; CREATE FUNCTION test=*# create table b (i int); CREATE TABLE test=*# insert into b values (5); INSERT 0 1 test=*# create table a (i int check(check_b())); CREATE TABLE test=*# insert into a values(10); INSERT 0 1 test=*# commit; COMMIT Okay. Now i make a Dump (it is a own schema called foo, not a whole database). The dump is called 'foo.sql'. (pg_dump -n foo test > foo.sql) Now i tried to restore the schema: test=# set search_path=public; SET test=*# drop schema foo cascade; NOTICE: drop cascades to 3 other objects DETAIL: drop cascades to function foo.check_b() drop cascades to table foo.b drop cascades to table foo.a DROP SCHEMA test=*# commit; COMMIT test=# \i foo.sql SET SET SET SET SET SET CREATE SCHEMA ALTER SCHEMA SET CREATE FUNCTION ALTER FUNCTION SET SET CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE psql:foo.sql:67: ERROR: new row for relation "a" violates check constraint "a_check" CONTEXT: COPY a, line 1: "10" I know, i can use pg_dump with -F c, and later i can create a listefile, reorder the objects in this listfile and pg_restore -L to solve that problem. But maybe pg_dump should first create the table without the check-constraint, fill all tables and create this check-constraint at the end. (in the same manner as foreign-key constraints) And yes, version ist 8.4.1 Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) -- 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] Boolean storage takes up 1 byte?
2009/10/1 Sam Mason > > bool_or and bool_and are aggregates that work over boolean data types. > > Ah yes, that makes total sense! I knew max wouldn't be logical in such as case, but couldn't think of the alternative. Thanks! > > I believe it's more to do with the fact that if you add a boolean column > and then subsequently an int column then you're going to struggle to > "pack" them efficiently. PG always puts columns on the "end" so that you > can add a column in constant time (i.e. no need to rewrite the table > in some common situations). Once you start doing this then packing is > awkward and a single byte becomes much easier. Whether the value is > NULL is stored elsewhere in the row. > > That's clear now. > > > And does its storage as a byte affect indexing or query planning? > > Not sure which aspects you're referring to here, sorry. > > Giving my question more thought, I believe it's pointless. You've answered my question. Thanks Sam.
Re: [GENERAL] Boolean storage takes up 1 byte?
On Thu, Oct 01, 2009 at 11:37:40AM +0100, Thom Brown wrote: > I've read the PostgreSQL documentation page on the boolean datatype ( > http://www.postgresql.org/docs/8.4/static/datatype-boolean.html) to find out > what PostgreSQL's definition of a boolean is, as I believe it is distinctive > from a bit(1) datatype Yup, they're really for different things. AND, OR and NOT are defined for the BOOLEAN datatype and not for bit strings. > (as you can't max() a boolean.. not sure what an > efficient alternative to that is). bool_or and bool_and are aggregates that work over boolean data types. > However, I see that a boolean takes up 1 > byte of storage, which is 8 bits. Is this due to the fact that the value > can be null? I believe it's more to do with the fact that if you add a boolean column and then subsequently an int column then you're going to struggle to "pack" them efficiently. PG always puts columns on the "end" so that you can add a column in constant time (i.e. no need to rewrite the table in some common situations). Once you start doing this then packing is awkward and a single byte becomes much easier. Whether the value is NULL is stored elsewhere in the row. Yes, this could be made more efficient; whether it's worth it is a difficult question! > And does its storage as a byte affect indexing or query planning? Not sure which aspects you're referring to here, sorry. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Boolean storage takes up 1 byte?
Hi, I've read the PostgreSQL documentation page on the boolean datatype ( http://www.postgresql.org/docs/8.4/static/datatype-boolean.html) to find out what PostgreSQL's definition of a boolean is, as I believe it is distinctive from a bit(1) datatype (as you can't max() a boolean.. not sure what an efficient alternative to that is). However, I see that a boolean takes up 1 byte of storage, which is 8 bits. Is this due to the fact that the value can be null? I'm not clear as to how a null field is stored, or is that the point... nothing references is so it is defined as null? If that is the case, can't this be stored as 1 bit? And does its storage as a byte affect indexing or query planning? Thom
[GENERAL] [OT] Relocation lookup
Hi all, Given the Social and Political reality in my country (Madagascar), I am obliged to look for a relocation. This is my public profile: http://www.linkedin.com/in/mihaminarakotomandimby Would you be aware of a position I could fit in? Thank you. -- Architecte Informatique chez Blueline/Gulfsat: Administration Systeme, Recherche & Developpement +261 34 29 155 34 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
