Re: [BUGS] BUG #8516: Calling VOLATILE from STABLE function
'Bruce Momjian' writes: > Well, we can't walk the function tree to know all called functions, and > those they call, so we don't even try. Inter function dependencies is a hard topic indeed. I still would like to see some kind of progress being made someday. The general case is turing complete tho, because you can use EXECUTE against programatically generated SQL. You could even generate a CREATE FUNCTION command from within a PL function and EXECUTE it then call the created function… and I think I've seen people do that in the past. Still some kind of limited in scope static analysis for the cases where it's possible to do so would be great. With pg_depend tracking so that you know you're doing something wrong at DROP FUNCTION time. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7575: "between" does not work properly with inet/cidr addresses
Mr Dash Four writes: > That isn't possible in my case, because on the right side I have IP ranges > (from-to) and currently there isn't a PostgreSQL function which converts IP > ranges to cidr/inet, but even if there was such function available, this may > produce more than one cidr/inet ranges, so it won't be of much use. Have you tried using the ip4r extension yet? Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6704: ALTER EXTENSION postgis SET SCHEMA leaves dangling relations
Dimitri Fontaine writes:
> Side note: as soon as we have CREATE EXTENSION AS $$ script $$; we will
> be able to add those cases as regression tests. That's not the main
> usage of that feature, by far, but I can't resits the occasion :)
Oh, I did already mention it :)
> I have some local failures in `make check` that I'm not sure originate
> from that patch. Still wanted to have an opinion about the idea before
> cleaning up.
Sorry for sending unfinished preliminary version, I just had the
opportunity to look at what happened: views will create a composite type
that needs its pg_class row updated when doing ALTER VIEW SET SCHEMA.
That means that we need proper tracking for that operation even when it
happens outside of an extension update script, as in the attached
version 4 of the patch.
I think the way forward is to use the simplest one for back branches and
this one for master only, unless it is appreciated of light enough
impact, right? (provided it's ok, too)
git diff --stat
src/backend/commands/alter.c | 14 +
src/backend/commands/extension.c | 48 +--
src/backend/commands/tablecmds.c | 122
+++---
src/backend/commands/typecmds.c | 33 +-
src/include/commands/alter.h |4 +-
src/include/commands/tablecmds.h |7 ++-
src/include/commands/typecmds.h |6 +-
7 files changed, 161 insertions(+), 73 deletions(-)
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
*** a/src/backend/commands/alter.c
--- b/src/backend/commands/alter.c
***
*** 247,253 ExecAlterObjectSchemaStmt(AlterObjectSchemaStmt *stmt)
* object doesn't have a schema.
*/
Oid
! AlterObjectNamespace_oid(Oid classId, Oid objid, Oid nspOid)
{
Oid oldNspOid = InvalidOid;
ObjectAddress dep;
--- 247,254
* object doesn't have a schema.
*/
Oid
! AlterObjectNamespace_oid(Oid classId, Oid objid, Oid nspOid,
! ObjectAddresses *objsMoved)
{
Oid oldNspOid = InvalidOid;
ObjectAddress dep;
***
*** 261,280 AlterObjectNamespace_oid(Oid classId, Oid objid, Oid nspOid)
case OCLASS_CLASS:
{
Relation rel;
- Relation classRel;
rel = relation_open(objid, AccessExclusiveLock);
oldNspOid = RelationGetNamespace(rel);
! classRel = heap_open(RelationRelationId, RowExclusiveLock);
!
! AlterRelationNamespaceInternal(classRel,
! objid,
! oldNspOid,
! nspOid,
! true);
!
! heap_close(classRel, RowExclusiveLock);
relation_close(rel, NoLock);
break;
--- 262,272
case OCLASS_CLASS:
{
Relation rel;
rel = relation_open(objid, AccessExclusiveLock);
oldNspOid = RelationGetNamespace(rel);
! AlterTableNamespaceInternal(rel, oldNspOid, nspOid, objsMoved);
relation_close(rel, NoLock);
break;
*** a/src/backend/commands/extension.c
--- b/src/backend/commands/extension.c
***
*** 2203,2208 AlterExtensionNamespace(List *names, const char *newschema)
--- 2203,2209
Relation depRel;
SysScanDesc depScan;
HeapTuple depTup;
+ ObjectAddresses *objsMoved;
if (list_length(names) != 1)
ereport(ERROR,
***
*** 2277,2282 AlterExtensionNamespace(List *names, const char *newschema)
--- 2278,2285
errmsg("extension \"%s\" does not support SET SCHEMA",
NameStr(extForm->extname;
+ objsMoved = new_object_addresses();
+
/*
* Scan pg_depend to find objects that depend directly on the extension,
* and alter each one's schema.
***
*** 2316,2343 AlterExtensionNamespace(List *names, const char *newschema)
if (dep.objectSubId != 0) /* should not happen */
elog(ERROR, "extension should not have a sub-object dependency");
! dep_oldNspOid = AlterObjectNamespace_oid(dep.classId,
! dep.objectId,
! nspOid);
! /*
! * Remember previous namespace of first object that has one
! */
! if (oldNspOid == InvalidOid && dep_oldNspOid != InvalidOid)
! oldNspOid = dep_oldNspOid;
! /*
! * If not all the objects had the same old namespace (ignoring any
! * that are not in namespaces), complain.
! */
! if (dep_oldNspOid != InvalidOid && dep_oldNspOid != oldNspOid)
! ereport(ERROR,
! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("extension \"%s\" does not support SET SCHEMA",
! NameStr(extForm->extname)),
! errdetail("%s is not in the extension's schema \"%s\"",
! getObjectDescription(&dep),
! get_namespace_name(oldNspOid;
}
systable_endscan(depScan);
--- 2319,2351
if (dep.objectSubId != 0) /* should not hap
Re: [BUGS] BUG #6704: ALTER EXTENSION postgis SET SCHEMA leaves dangling relations
Tom Lane writes:
> I've been testing this patch with an extension having this definition
> file:
Side note: as soon as we have CREATE EXTENSION AS $$ script $$; we will
be able to add those cases as regression tests. That's not the main
usage of that feature, by far, but I can't resits the occasion :)
> -
> create table t1(f1 serial primary key, f2 text);
>
> create table t2(f1 int primary key, f2 text);
>
> create sequence ss2;
>
> alter sequence ss2 owned by t2.f1;
>
> create sequence ss3;
>
> create table t3(f1 int primary key, f2 text);
>
> alter sequence ss3 owned by t3.f1;
> -
This exact same script pass with the attached patch, a patch on top
Álvaro's version:
dim=# create extension extseq;
CREATE EXTENSION
dim=# create schema foo;
CREATE SCHEMA
dim=# alter extension extseq set schema foo;
ALTER EXTENSION
dim=# \dx+ extseq
Objects in extension "extseq"
Object Description
sequence foo.ss2
sequence foo.ss3
sequence foo.t1_f1_seq
table foo.t1
table foo.t2
table foo.t3
(6 rows)
I have some local failures in `make check` that I'm not sure originate
from that patch. Still wanted to have an opinion about the idea before
cleaning up.
> nothing if the sequence was already moved. We could maybe refactor
> so that AlterRelationNamespaceInternal's test covers the type case too,
> but I don't think that is the way forward, because it won't cover any
> non-sequence cases where a type is reached through two different
> dependency paths.
I tried to care about that in the attached. Spent so much time rolling
it in my head in every possible angle that I really need another pair of
eyes on it though.
> So it appears to me that a real fix involves extending the check and
> add logic to at least relations and types, and perhaps eventually to
> everything that AlterObjectNamespace_oid can call. That's fairly
> invasive, especially if we try to do the whole nine yards immediately.
> But perhaps for now we need only fix the relation and type cases.
I think INDEX and CONSTRAINTS (the only other things that can be called
from that point) are safe because there's no explicit support for them
in the AlterObjectNamespace_oid() function.
> BTW, I experimented with inserting CommandCounterIncrement calls
> into the AlterExtensionNamespace loop, and eventually decided that
> that's probably not the best path to a solution. The killer problem is
> that it messes up the logic in AlterExtensionNamespace that tries to
> verify that all the objects had been in the same namespace. If the
> subroutines report that the object is now in the target namespace,
> is that okay or not? You can't tell.
Agreed.
> I think that the right way to proceed is to *not* do
> CommandCounterIncrement in the AlterExtensionNamespace loop, and also
> *not* have a test in AlterExtensionNamespace for an object already
> having been moved. Rather, since we already know we need that test down
> in AlterRelationNamespaceInternal and AlterTypeNamespaceInternal, do it
> only at that level. This combination of choices ensures that we'll get
> back valid reports of the old namespace for each object, and so the
> are-they-all-the-same logic in AlterExtensionNamespace still works.
See attached.
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
*** a/src/backend/commands/alter.c
--- b/src/backend/commands/alter.c
***
*** 247,253 ExecAlterObjectSchemaStmt(AlterObjectSchemaStmt *stmt)
* object doesn't have a schema.
*/
Oid
! AlterObjectNamespace_oid(Oid classId, Oid objid, Oid nspOid)
{
Oid oldNspOid = InvalidOid;
ObjectAddress dep;
--- 247,254
* object doesn't have a schema.
*/
Oid
! AlterObjectNamespace_oid(Oid classId, Oid objid, Oid nspOid,
! ObjectAddresses *objsMoved)
{
Oid oldNspOid = InvalidOid;
ObjectAddress dep;
***
*** 261,280 AlterObjectNamespace_oid(Oid classId, Oid objid, Oid nspOid)
case OCLASS_CLASS:
{
Relation rel;
- Relation classRel;
rel = relation_open(objid, AccessExclusiveLock);
oldNspOid = RelationGetNamespace(rel);
! classRel = heap_open(RelationRelationId, RowExclusiveLock);
!
! AlterRelationNamespaceInternal(classRel,
! objid,
! oldNspOid,
! nspOid,
! true);
!
! heap_close(classRel, RowExclusiveLock);
relation_close(rel, NoLock);
break;
--- 262,272
case OCLASS_CLASS:
{
Relation rel;
rel = relation_open(objid, AccessExclusiveLock);
oldNspOid = RelationGetNamespace(rel);
! AlterTableNamespaceInternal(rel, oldNspO
Re: [BUGS] BUG #6704: ALTER EXTENSION postgis SET SCHEMA leaves dangling relations
Tom Lane writes: > I've got to say that I think this is fundamentally the wrong approach: > rather than fixing the generic problem of ALTER EXTENSION not coping > with multiple dependency paths to the same object, it hacks the specific > case of owned sequences, and what's more it does that by assuming that > every owned sequence *will* have a dependency on the extension. That's > not a safe assumption. In general, agreed. > Still, this might be the best approach for the back branches, given that > we do not know of any existing multiple-dependency scenarios other than > the owned-sequence case. A real fix is looking mighty invasive. That's what I was aiming at, best approach for the back branches. >> Even for TIP I don't want us to change how pg_depend tracking is done, > > Agreed. Quite aside from backwards-compatibility concerns, I think that > trying to avoid multiple dependency paths is doomed to failure. For a “DIRTT” approach to the problems, I think Álvaro's work is in the right direction, and should be pursued without trying to address the back branches too. I don't remember now if his tracking attempt was also trying to change pg_depend entries, I think that was in two separate patches versions. DIRTT: Do It Right This Time Álvaro, do you want to be working on a master only version of the fix or do you want me to? Regards, -- Dimitri Fontaine06 63 07 10 78 http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6704: ALTER EXTENSION postgis SET SCHEMA leaves dangling relations
Dimitri Fontaine writes:
> I think we shouldn't change the content of pg_depend lightly here, and
So here's a patch following that idea.
Even for TIP I don't want us to change how pg_depend tracking is done,
because I want to propose a fix for the pg_dump bug wrt sequences and
pg_extension_config_dump() wherein you can actually register a sequence
(owned by a table or not) but then pg_dump fails to dump it (see report
from Marko Kreen)
http://archives.postgresql.org/message-id/cacmqxcjjauc9jpa64vxskrn67byjuymodz-mgy-_aoz6erg...@mail.gmail.com
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
*** a/src/backend/commands/alter.c
--- b/src/backend/commands/alter.c
***
*** 18,23
--- 18,24
#include "catalog/dependency.h"
#include "catalog/indexing.h"
#include "catalog/namespace.h"
+ #include "catalog/pg_constraint.h"
#include "catalog/pg_largeobject.h"
#include "catalog/pg_namespace.h"
#include "commands/alter.h"
***
*** 268,278 AlterObjectNamespace_oid(Oid classId, Oid objid, Oid nspOid)
classRel = heap_open(RelationRelationId, RowExclusiveLock);
! AlterRelationNamespaceInternal(classRel,
! objid,
! oldNspOid,
! nspOid,
! true);
heap_close(classRel, RowExclusiveLock);
--- 269,282
classRel = heap_open(RelationRelationId, RowExclusiveLock);
! AlterRelationNamespace_oid(rel,
! classRel,
! objid,
! oldNspOid,
! nspOid,
! true,
! false,
! NULL);
heap_close(classRel, RowExclusiveLock);
*** a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c
***
*** 260,267 static void StoreCatalogInheritance(Oid relationId, List *supers);
static void StoreCatalogInheritance1(Oid relationId, Oid parentOid,
int16 seqNumber, Relation inhRelation);
static int findAttrByName(const char *attributeName, List *schema);
- static void AlterIndexNamespaces(Relation classRel, Relation rel,
- Oid oldNspOid, Oid newNspOid);
static void AlterSeqNamespaces(Relation classRel, Relation rel,
Oid oldNspOid, Oid newNspOid,
const char *newNspName, LOCKMODE lockmode);
--- 260,265
***
*** 9755,9761 AlterTableNamespace(AlterObjectSchemaStmt *stmt)
/* OK, modify the pg_class row and pg_depend entry */
classRel = heap_open(RelationRelationId, RowExclusiveLock);
! AlterRelationNamespaceInternal(classRel, relid, oldNspOid, nspOid, true);
/* Fix the table's row type too */
AlterTypeNamespaceInternal(rel->rd_rel->reltype, nspOid, false, false);
--- 9753,9789
/* OK, modify the pg_class row and pg_depend entry */
classRel = heap_open(RelationRelationId, RowExclusiveLock);
! AlterRelationNamespace_oid(rel,
! classRel,
! relid,
! oldNspOid,
! nspOid,
! true,
! true,
! stmt->newschema);
!
! heap_close(classRel, RowExclusiveLock);
!
! /* close rel, but keep lock until commit */
! relation_close(rel, NoLock);
! }
!
! /*
! * Relocating a relation to another namespace, and its related objects too.
! *
! * Extensions track both the main table and its owned sequences so it's not
! * necessary to alter the namespace of those sequences when doing ALTER
! * EXTENSION ... SET SCHEMA. alterSeqNamespaces is then false and newschema is
! * NULL.
! */
! void
! AlterRelationNamespace_oid(Relation rel, Relation classRel, Oid relOid,
! Oid oldNspOid, Oid nspOid,
! bool hasDependEntry,
! bool alterSeqNamespaces,
! char *newschema)
! {
! AlterRelationNamespaceInternal(classRel, relOid, oldNspOid, nspOid, true);
/* Fix the table's row type too */
AlterTypeNamespaceInternal(rel->rd_rel->reltype, nspOid, false, false);
***
*** 9764,9778 AlterTableNamespace(AlterObjectSchemaStmt *stmt)
if (rel->rd_rel->relkind == RELKIND_RELATION)
{
AlterIndexNamespaces(classRel, rel, oldNspOid, nspOid);
- AlterSeqNamespaces(classRel, rel, oldNspOid, nspOid, stmt->newschema,
- AccessExclusiveLock);
- AlterConstraintNamespaces(relid, oldNspOid, nspOid, false);
- }
-
- heap_close(classRel, RowExclusiveLock);
! /* close rel, but keep lock until commit */
! relation_close(rel, NoLock);
}
/*
--- 9792,9804
if (rel->rd_rel->relkind == RELKIND_RELATION)
{
AlterIndexNamespaces(classRel, rel, oldNspOid, nspOid);
! if (alterSeqNamespaces)
! AlterSeqNamespaces(classRel, rel, oldNspOid, nspOid,
! newschema,
! AccessExclusiveLock);
! AlterConstraintNamespaces(relOid, oldNspOid, nspOid, false);
! }
}
/*
Re: [BUGS] BUG #6704: ALTER EXTENSION postgis SET SCHEMA leaves dangling relations
Alvaro Herrera writes: > Well, what I saw was that both the table and its SERIAL-generated > sequence got an DEPENDENCY_EXTENSION row in pg_depend, which is exactly > what (IMV) causes the problem. One of my proposals is to tweak the code > to avoid that row (but if we do that, then we need to do something about > databases that contain such rows today). Ah yes, indeed. I think we shouldn't change the content of pg_depend lightly here, and that we should rather specialize AlterObjectNamespace_oid() to skip caring about sequences. The other objects that get moved by AlterTableNamepace other than the table itself and its sequences are Indexes and Constraints. Owned Sequence (serial) will get cared of by the extension dependency walking code. I'm going to have a try at that. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6704: ALTER EXTENSION postgis SET SCHEMA leaves dangling relations
Hi, Sorry for being late at the party… been distracted away… Bruce Momjian writes: > On Fri, Jun 22, 2012 at 10:37:10PM -0400, Tom Lane wrote: >> j...@pgexperts.com writes: >> > DROP and CREATE extension appear to work fine, but if you ALTER EXTENSION >> > postgis SET SCHEMA foo, it leaves a few relations behind. >> >> What it seems to be leaving behind is indexes ... also relation rowtypes. >> >> A bit of looking shows that ALTER EXTENSION SET SCHEMA calls >> AlterObjectNamespace_oid on the table. AlterObjectNamespace_oid >> calls AlterRelationNamespaceInternal, and nothing else. In comparison, >> ALTER TABLE SET SCHEMA (AlterTableNamespace) calls >> AlterRelationNamespaceInternal and about four other things. I'm not >> sure if this was broken before the last round of refactoring in this >> area, but for sure it's broken now. Looking at that code, my theory of how we got there is that in the submitted extension patch I did only use DEPENDENCY_INTERNAL and Tom introduced the much better DEPENDENCY_EXTENSION tracking. With the former, indexes and sequences and constraints where found in the dependency walking code, but only the main relation is now registered in the later model. I need to do some testing about dependency tracking on SERIAL generated sequences compared to manually created sequences in extension scripts, I think we track sequences directly only in the manual case. I think we need to share more code in between AlterRelationNamespaceInternal and AlterTableNamespace, but I'm not sure if that's not exactly what Álvaro did try with his _oid() attempt that failed. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Extension tracking temp table and causing update failure
Tom Lane writes: > Actually, after I got done hacking the temp-schema case, I realized that > preventing temp tables from becoming extension members isn't so ugly as > I first thought; in fact, it's pretty much a one-liner, and much cleaner > than hacking ON COMMIT DROP. PFA a patch that fixes both of the > temp-table issues. Awesome. I'm surprised we have so few callers of NamespaceCreate, but that makes sense indeed. Nice localized patch, and I know why I want to upgrade to 9.1.4 sometime later :) (extensions with PL functions that you need to drop when the API changes, you need to alter extension drop function in the script before 9.1.4). Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Extension tracking temp table and causing update failure
Tom Lane writes: > 1. If you forget to drop the temp table before ending the script, > then when the session ends and the temp table is forcibly dropped, > the whole extension goes away (following the rule that a forced drop > of an extension member makes the whole extension go away). This is > mildly annoying, but since not dropping the temp table is a clear bug > in an extension script, I think we can live with it. Could we force temp tables created in an extension script to be ON COMMIT DROP so that CurrentExtensionObject is still set and your patch kicks in, preventing the DROP cascading? > 2. #1 applies only in the typical case where the backend's temp table > schema already exists. Otherwise, when we create the pg_temp_N schema > it gets listed as one of the extension's objects. Then, when you exit > the session, this happens (behind the scenes; it's logged in the > postmaster log but not shown to the client): > > FATAL: cannot drop schema pg_temp_2 because extension myext requires it > HINT: You can drop extension myext instead. Interesting. > This is really bad: any temp tables created in this session won't be > cleaned out. And the same for any temp tables created in future > sessions using the same backend ID slot, since they'll get the identical > error on exit. Even worse, if you decide to drop the extension, you > might be taking out temp tables that belong to some active session other > than your own. Given those hazards and the fact that there's no > reasonable way for an extension script to avoid the risk, I think this > one is a must-fix. > > I don't see any easy way around this one other than kluging temp-schema > creation to not link the temp schema to the active extension. Which is > exactly what I'd not wanted to do in ATRewriteTable. The one bright spot > about it is that temp-table schemas are inherently a special case > because of their weird creation process, so we could have some comfort > that there are probably not other similar bugs lurking. Yeah protecting against the temp schema special case (can't be registered as a dependency against an extension object) seems good to me, and I'm not able to think about a better answer here. We might want to protect in the same way against temp schema explicitly created by the extension script too (IIRC you can actually do that): it could be just about documentation, but if that's not too much contorting the code it would be better to just ERROR out, I think. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Extension tracking temp table and causing update failure
Tom Lane writes: > However, recordDependencyOnCurrentExtension doesn't know that the table > is meant to be transient and links it to the current extension; so when > the table gets dropped a bit later, the dependency code complains. > > [...] > > Instead, I'm tempted to propose that dependency.c explicitly allow drops > of objects that belong to the current extension, when an extension is > being created or updated. (That is, if we come across a dependency > reference to the active extension, we just ignore it. A quick look > suggests that this would require only a very small patch.) That would > prevent the entire class of problems. Thinking about it, what I think makes sense at the user level is that you can either DROP an extension's object in the extension script or detach it so that it still exists on its own. That means we still need to be able to ALTER EXTENSION … DROP and that this operation should be automatically handled when the extension's script contains a DROP command. The way to implement that seems to be exactly what you're saying. (So that view is mostly useful for how to document the behaviour). > It would also have the effect that explicit DROPs of member objects in > extension scripts could be done without an explicit ALTER EXTENSION DROP > first. I think we'd originally decided that requiring the ALTER was a > good safety feature, but is it really more than nanny-ism? The intent > of a DROP command seems pretty clear. What I remember we decided is that you can't DROP any single object of an extension alone, you have to drop the extension wholesale or not at all. So that you first “detach” the object from the extension then drop it. That makes perfect sense in general but is a useless restriction when executing an extension's script. I consider that bugfix for back branches, by the way (well, 9.1). Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6172: DROP EXTENSION error without CASCADE
Tom Lane writes: > The case where this would actually happen is where extension A creates > some operator, and mentions some other operator as its commutator or > negator, but never gets around to defining the other operator. Then > extension B comes along and tries to fill in the other operator > definition. Do we want to let that happen, or do we want to throw an > error on the grounds that this sort of interconnection of two extensions > was almost certainly not intended? (Note that I rather doubt that > dropping either extension alone, afterwards, would clean up nicely, > since we have no code that would remove the oprcom/oprnegate linkage.) I don't think we should let that happen. We currently support self contained extensions and I don't see opening the door this way as a feature. > On the whole I'm starting to think that throwing an error is the best > thing. We could always relax that later, but going the other way might > be problematic. +1 Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6172: DROP EXTENSION error without CASCADE
Tom Lane writes:
> I'm betting it's got something to do with
> http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=eb15f26d577a11319b9429fb84f752a0135918db
You're right, once more. Here's what I understand is happening from
reading the code. No patch attached, the scope of change I did is not
calling for one. I include full analysis in case you want to fix it in
another way, I could have missed something important here.
For reference, the error we're tracking begins with:
ERROR: cannot drop extension cube because other objects depend on it
DETAIL: operator <>(cube,cube) depends on function cube_ne(cube,cube)
The problem is that the following SQL will create the <> operator as a
Shell Operator then complete its definition later.
CREATE OPERATOR = (
LEFTARG = cube, RIGHTARG = cube, PROCEDURE = cube_eq,
COMMUTATOR = '=', NEGATOR = '<>',
RESTRICT = eqsel, JOIN = eqjoinsel,
MERGES
);
Here it's quite obvious that the '<>' operator (created as a shell) is
missing the dependency:
~:54320=# select oid, oprname from pg_operator
where oprleft = 'cube'::regtype and oprright = 'cube'::regtype and oprname in
('=', '<>');
oid | oprname
---+-
16421 | <>
16422 | =
(2 rows)
~:54320=# select * from pg_depend
where classid = 'pg_operator'::regclass and objid in (16421, 16422);
classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
-+---+--++--+-+-
2617 | 16421 |0 | 1255 |16393 | 0 | n
2617 | 16421 |0 | 1247 |16386 | 0 | n
2617 | 16421 |0 | 1247 |16386 | 0 | n
2617 | 16421 |0 | 2615 | 2200 | 0 | n
2617 | 16422 |0 | 3079 |16385 | 0 | e
2617 | 16422 |0 | 1255 |16392 | 0 | n
2617 | 16422 |0 | 1247 |16386 | 0 | n
2617 | 16422 |0 | 1247 |16386 | 0 | n
2617 | 16422 |0 | 2615 | 2200 | 0 | n
(9 rows)
The code in pg_operator.c records the dependency on the Extension both
for a shell operator (in OperatorShellMake) and for a complete operator,
in OperatorCreate.
But in makeOperatorDependencies() we find the following piece of code:
/* In case we are updating a shell, delete any existing entries */
deleteDependencyRecordsFor(myself.classId, myself.objectId, false);
false is for bool skipExtensionDeps.
And now at the end of the same function, dependency is recorded back,
except in some case:
oldext = getExtensionOfObject(object->classId,
object->objectId);
if (OidIsValid(oldext))
{
/* If already a member of this extension,
nothing to do */
if (oldext == CurrentExtensionObject)
return;
The problem lies in catalog scans and SnapshotNow, I think. My fix is
to have deleteDependencyRecordsFor use true for skipExtensionDeps. Then:
~:54320=# drop extension cube;
DROP EXTENSION
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6171: Sockets Issue
"Robert Hopek" writes: > coding), existing software, such as phpPgAdmin, are stuck only looking in > /tmp for it. Did you try host=/custom/path/to/the/socket/? Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6172: DROP EXTENSION error without CASCADE
Dimitri Fontaine writes: > Also, \dx+ cube will not show all the operators and functions. Some dependency information is indeed missing in pg_depend. Will look at why tomorrow, day's over here. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6172: DROP EXTENSION error without CASCADE
"Hitoshi Harada" writes: > On pure-installed RC1 database, you can CREATE EXTENSION, but cannot DROP > it. > > CREATE EXTENSION cube; > DROP EXTENSION cube; > > ERROR: cannot drop extension cube because other objects depend on it I confirm I have the same bug in current HEAD. Reading the code, my gut feeling is that the bug sits in findDependentObjects(), in this part of it: /* * Okay, recurse to the other object instead of proceeding. We * treat this exactly as if the original reference had linked * to that object instead of this one; hence, pass through the * same flags and stack. */ The extension cube depends on some operator that depend on some function implementing them, and as the initial dependency delete call is not explicitly mentioning them, then it behaves as if CASCADE was needed. Also, \dx+ cube will not show all the operators and functions. It skips those that we see in the CASCADE error message listing. Here's the SQL query that will list the object with a direct extension dependency towards the extension, here of OID 16385. dim=# SELECT pg_catalog.pg_describe_object(classid, objid, 0) AS "Object Description" dim-# FROM pg_catalog.pg_depend dim-# WHERE refclassid = 'pg_catalog.pg_extension'::pg_catalog.regclass AND refobjid = '16385' AND deptype = 'e' dim-# ORDER BY 1; Of course we didn't have that problem when we added extensions in (that I remember of), so I'm now going to try and find when that did change… Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5946: Long exclusive lock taken by vacuum (not full)
Greg Stark writes: > So is it ok for inserting one row to cause my table to grow by 90GB? > Or should there be some maximum size increment at which it stops > growing? What should that maximum be? What if I'm on a big raid system > where that size doesn't even add a block to every stripe element? I'd think that capping the idea to the segment size makes sense. Also, what about having a background process (bgwriter or autovacuum come to mind) doing the work, rather than the backend that happens to be inserting the row? It could send a message, and continue creating a newer 8kb block if the background process has not yet extended the storage. Also, to be safe I guess we could arrange to have the new segment be created way before reaching the very end of the relation (so that adding 8kb does not need to create a new segment, so as to avoid a race condition with the background process doing so itself). Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Mismapping of Mountain Time
Josh Berkus writes: > echo $TZ returns nothing. We've checked several Ubuntu systems, and it > seems that Ubuntu does not set $TZ. You can tweak that easily by editing /etc/postgresql/8.4/main/environment. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5867: wish: plpgsql print table for debug
Tom Lane writes: > Note that doing anything more than RAISE NOTICE or equivalent would > imply a significant protocol change. My understanding is that the standard allows multiple resultsets per query, is that the protocol change you're talking about? Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Portable, simple means of determining distribution directory
Steve White writes: > It would be advantageous to have a portable, simple means of including > a module from the distribution contrib/ directory. Please have a look at the Extension patch section here: https://commitfest.postgresql.org/action/commitfest_view?id=9 Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5740: contrib/spi/moddatetime.c doesn't work with timezones.
Tom Lane writes: > I don't actually see any point in having two functions at all. Since > the trigger is examining the column type internally, it could perfectly > well do the right thing at runtime depending on column type. Sorry, brain fart from me. Didn't realise we're talking about a trigger that looks at the column type here, it was a misplaced general advice. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5740: contrib/spi/moddatetime.c doesn't work with timezones.
Dirk Heinrichs writes: > Please find the patch attached. It's against 8.4.5. > > extern Datum moddatetime(PG_FUNCTION_ARGS); > +extern Datum moddatetimetz(PG_FUNCTION_ARGS); [...] > +CREATE OR REPLACE FUNCTION moddatetimetz() > +RETURNS trigger > +AS 'MODULE_PATHNAME' > +LANGUAGE C; You can also do AS 'MODULE_PATHNAME', 'c_function_name' and maintain a single C function that copes with both timestamp and timestamptz arguments, that's what I had in mind: you still declare two functions at the SQL level, though. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5740: contrib/spi/moddatetime.c doesn't work with timezones.
"Dirk Heinrichs" writes:
> The moddatetime function provided by this module only works on columns of
> type "timestamp without time zone". Would be nice if it could also provide
> an analogous function moddatetime_tz which provides the same functionality
> for columns of type "timestamp with time zone".
dim=# select pg_typeof('now'::timestamp), pg_typeof('now'::timestamptz);
pg_typeof |pg_typeof
-+--
timestamp without time zone | timestamp with time zone
(1 row)
So I guess that you need to modify very little code to get the trigger
to work for both types.
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] index on function confuses drop table cascade on child
Tom Lane writes: > Any thoughts out there? Color me slow, but I don't understand what allows an index creation on a table to not systematically add a dependency entry for the index that references the table. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5736: 9.0.1 segmentation fault (sig11) during long-lived update
Tom Lane writes: > Also, make sure the ulimit command is effective in the shell that will > actually launch the postmaster. This can be tricky if your PG launch > script uses "su". If you're using the RH or PGDG RPMs' initscript, > I'd suggest putting the ulimit command in ~postgres/.bash_profile. Under debian or ubuntu you can add the -c switch to pg_ctl options so that IIRC it will change the ulimit settings for you. An example on a 8.4 install: $ cat /etc/postgresql/8.4/main/pg_ctl.conf # Automatic pg_ctl configuration # This configuration file contains cluster specific options to be passed to # pg_ctl(1). pg_ctl_options = '' Also see the /etc/postgresql/X.Y/cluster/environment file. For archives sake and people following without a debian based installation handy: $ cat /etc/postgresql/8.4/main/environment # environment variables for postmaster process # This file has the same syntax as postgresql.conf: # VARIABLE = simple_value # VARIABLE2 = 'any value!' # I. e. you need to enclose any value which does not only consist of letters, # numbers, and '-', '_', '.' in single quotes. Shell commands are not # evaluated. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5687: RADIUS Authentication issues
Alan T DeKok writes: > Magnus Hagander wrote: >> If you can test the complete patch in your environment (particularly >> if you already have a "bad packet injector" that you know creates the >> issue on 9.0), that would be great though. > > If you use FreeRADIUS, use "radclient" to send the following text: > > User-Name = "bob" > User-Password = "hello" > Raw-Attribute = 0x0501 > > The last bit is a malformed RADIUS attribute. Would using this lib help here? http://caca.zoy.org/wiki/zzuf Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] installer problems
Dave Page writes: > On Fri, Sep 24, 2010 at 6:28 AM, Peter Eisentraut wrote: >> On tor, 2010-09-23 at 23:56 +0100, Dave Page wrote: >>> > Surely the error message can be polished, though? > > How else would you suggest we find out what may go wrong on an > unsupported platform? My guess here is that you could use `lsb_release -a` or some other system dependant facilities to know the current platform, then error out with a nice error message if it's not one you know you support. This way the installer won't even try to run and won't go wrong on an unsupported platform… Regards, -- dim -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5532: Valid UTF8 sequence errors as invalid
Tom Lane writes: > (BTW, I should think that iconv or some related tool would have a > solution for fixing this miscoding; it's not an uncommon problem.) I guess recode is handling that. http://recode.progiciels-bpi.ca/manual/Universal.html#Universal Regards, -- dim -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names -> pg_restore may fail when upgrading
Robert Haas writes: > Suppose I have a server running 8.2 and I'm going to wipe it and > install the latest version of $DISTRIBUTION which bundles 8.4. [...] > I'm sure someone will tell me my system administration practices suck, > but people do these kinds of things, in real life, all the time. > Maybe if we all had an IQ of 170 and an infinite hardware budget we > wouldn't, but my IQ is only 169. :-) So why aren't you using a distribution that ease the pain by supporting exactly your use case? :) Regards, -- dim -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names -> pg_restore may fail when upgrading
Stephen Frost writes: > If it's an option w/ a default of "off", then chances are the admin will > get the failure you're talking about, realize there's an issue, but then > have a way to actually *fix* the restore without having to hack up > multi-gigabyte files using vi. If you'd like, you're welcome to > consider the default of "off" as a feature. > > +1 from me for adding the option. +1 too. Seems I've been unclear, my thinking is that this option is not a solution for everyone, but I do agree that it will help in some cases. And in those cases you'll be so happy to have it! Regards, -- dim -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names -> pg_restore may fail when upgrading
Top posting, sorry for that. -- dim Le 10 juin 2010 à 03:40, Robert Haas a écrit : On Wed, Jun 9, 2010 at 9:35 PM, Bruce Momjian wrote: Robert Haas wrote: I think users would rather have the restore fail, and know right away they have an issue, than to do the upgrade, and find out later that some of their application queries fail and they need to run around fixing them. ?(FYI, pg_upgrade would use the new pg_dump and would not fail.) In a way, the fact that the restore fails can be seen as a feature --- they get the error before the go live on 8.4. ?(Yeah, I am serious.) +1 Eeh, I've had this happen to me on earlier releases, and it didn't feel like a feature to me. YMMV, of course. Would you have preferred later application failure? YES! It's a heck of a lot easier to fix the application than it is to doctor the dump output with vi. But of course you don't ever do that. What you do once the restore failed on you is fix the schema and the application before to upgrade. At least you have a chance to upgrade to a working setup. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Re: BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail
Craig Ringer writes: >> - other products are happy to solve the DISTINCT restriction without >> any hint as far as what the datatype really is > > ... and so is Pg. That's not the problem; Pg complains after resolving the > SELECT DISTINCT, when it finds that it's trying to insert values of type > text' into a column with type 'timestamp'. Ah yes. I've been paying more attention to the energy people have been willing to put into helping the OP than into what the problem really is. Thanks for clarifying. -- dim -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Re: BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail
Farid Zidan writes: > I see no technical analysis in your response. I made a clear reference to what others have been saying, and that you have been refusing to read. I don't see any point in getting technical again, as I'm sure you will refuse to understand what is happening to you here. But as you insist, let's try once more: - your consider a bug where PostgreSQL wants to know more about the data type you're using in the query in order to be able to enforce a DISTINCT restriction - other products are happy to solve the DISTINCT restriction without any hint as far as what the datatype really is - the error message is perfectly clear about what PostgreSQL needs from you - the reason why PostgreSQL wants you to give it details is clear to: what means DISTINCT depends on the datatype, you can easily have two different text representations of the same timestamptz, for example - it could be considered a possible area of improvement in the system that the SELECT part of the INSERT INTO ... SELECT ... could determine the SELECT output columns type by looking at the INSERT target table definition - it would be asking for a new feature to be considered, not for a bug to be solved: the system currently works exactly as designed. Now if you keep wanting not to understand how the system is currently designed, that makes it your problem, no one else. Regards, -- dim -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names -> pg_restore may fail when upgrading
Robert Haas writes: >> Well as Bruce said this option won't solve the OP's problem, unless the >> application he's using for managing the backups do use the option. > > Well, that's a pretty trivial change to the backup script. +1 from me on > providing a pg_dump option. The application still have to have been using the option in the past. -- dim -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Re: BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail
Farid Zidan writes: > I am not asking you to re-write my sql so the bug will not show. I am > presenting you with sql that fails and shows the bug. If every time > someone reported a bug you ask them to re-write their sql so the bug > is not hit, that would not eliminate the bug. You're not reading us. Your example makes no sense at all, and hides the problem you have. You are failing to understand the situation because of this. If you want to understand the problem you have, please go test your 14 databases with the meaningful tests that have been offered to you. Until and unless you do so, I don't think posting here again on this issue has any value, for you nor for us. You're abusing the time of lots of people who are both busy and helpful, so please try to understand the advices and analysis they've been offering to you. Regards, -- dim -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names -> pg_restore may fail when upgrading
Alvaro Herrera writes: > I don't think "dumps must be human-readable" is an argument to reject > such a switch, as long as it's off by default. And I haven't seen any > other valid argument either, so +1 from me. Well as Bruce said this option won't solve the OP's problem, unless the application he's using for managing the backups do use the option. The current solution seems to be to use a packaging system that allows installing several major versions at any time, and use this ability to properly migrate data. Hint: debian does just this. http://manpages.debian.net/cgi-bin/man.cgi?query=pg_upgradecluster http://manpages.ubuntu.com/manpages/hardy/man8/pg_upgradecluster.8.html Regards, -- dim -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] bugs that have not been replied-to on list
Stefan Kaltenbrunner writes: > As in have a (hyptothetical) tracker being subscribed to -bugs (and maybe > the other lists in the future as well) so the workflow would look like > this: Well there is a WIP to use an ArchiveOpteryX based solution to replace the archives and get rid of the artificial breaking of pages. My guess is that adding a status table linking to emails and that a set of volunteers (they gave their names!) maintain would make our day. > 1a. if somebody submits a request through the webform the tracker assigns an > id and can automatically track all responses on the list The AOX archive based system has a nice thread view based on some CTE queries. Assigning an ID to emails that are not a response shouldn't be hard to do in a trigger if necessary, and parsing the email subject line for cases when the ID exists looks feasible too. > 1b. if somebody submits directly to -bugs we could either have the tracker > automatically create an id and track it or we could have a trivial interface > to take a message-id and import on demand AOX will just archive the mail in its PostgreSQL database upon receiving, it's just another subscriber to the list. > 2a. we can simply have the tracker export a dashboard status of: > > *) stuff that had no reply too (which is one of the open questions) > *) if a commit has the bug id we could have it autoclose/autotrack that as > well That would be a set of queries with some dynamic web scripting around. Plus the all the work to get to a usable WebUI of course. > 2b. for the case of "not a bug"/"added to TODO"/"works as > intended"/"pgadmin"/"JDBC" - we would either have to do a trival web > interface to claim so or people could send status updates inline in the > mail(at least the BZ emailinterface can take commands like "@close NOTABUG" > or whatever) > > 2c. if a bug gets a reply but will never result in a solution per 2a or 2b > we could add other dashboard as in "bug replied but no conclusion yet" The triage would have to be manual. Or we could have some nice Tsearch based queries to parse the mail content and offer an AI based dashboard of waiting bugs. Sounds fun, he? > Implementing this on our own (if that is about the workflow we want) is > probably not even a lot of work, but we could also use an existing solution > just as the backend engine and do the frontends ourselfs. Did I mention AOX and the work in progress for the archives? :) -- dim -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] dividing money by money
Chris Travers writes: > Ok. Here is my application: I write a multi-currency accounting > program backed by PostgreSQL. After 1.3 is released (2Q this year), > we expect to be doing a full redesign. > > What I am thinking about is having a custom data type, something like: I'd be interested into participating into such a project, in particular a multi-currencies support type coded in C would be useful for me. > CREATE DOMAIN curr VARCHAR(3); > CREATE TYPE monetary AS (amount NUMERIC, currency CURR, multiplier > NUMERIC); This reduces into two basic components: a value (amount * > multiplier) and a currency identifier (USD, etc). > > One could also then store monetary[] arrays for addressing specific > denomination storage. I.e. "When closing the till we had 26 pennies, > 53 nickles, 12 quarters, 25 $1 bills, 35 $5 bills, 15 $10 bills, and 5 > $20 bills." > > Then we can allow NUMERIC arithmetic on monetary amounts provided that > the CURR field is the same. We could also store things like the cash > counted from a till at the end of the day by denomination. One could > have easy monetary::numeric casts as well. Sounds a good starting point, but it sounds like we'll have to think about it to see how it survive a more detailed approach. > Anyway, that's my basic thinking. One could further add currency > conversion tables to an application if necessary. That's where it become interesting. Finding a nice way to solve the problem of more than one currency in the same table, with dated (timestamped?) conversion rates that are possibly unknown at INSERT time… -- dim -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5389: Column order on dump/reload broken from defined setof function
"Timothy Seever" writes: > CREATE FUNCTION testfunc() RETURNS setof tablec AS $BODY$ SELECT * FROM > tablec; $BODY$ LANGUAGE 'sql' STABLE; Common wisdom saith to never ever use "SELECT *" in your code. You just found out a reason why. Regards, -- dim -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Bug report: Wrong version in pg_config
Tom Lane writes: > You would need to take that up with whoever packages Postgres for > Ubuntu. It sounds like they have some glitch in the package setup. > AFAIK, whoever that is doesn't read this list, which is mainly for > upstream Postgres development. I think Martin Pitt reads this list, he's maintaining both for ubuntu and debian. The problem itself is tied to the multi-major version support in the packaging, and in the tools. # dpkg -S /usr/bin/pg_config libpq-dev: /usr/bin/pg_config The libpq-dev should be independant of the major version you target, but contains pg_config. Using the direct path works too: postgresql-server-dev-8.4: /usr/lib/postgresql/8.4/bin/pg_config postgresql-server-dev-8.3: /usr/lib/postgresql/8.3/bin/pg_config You could even adjust your PATH to prepend it with the bin tools you need for a specific debian major. Regards, -- dim -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Bug report: Wrong version in pg_config
Adam Matan writes: > But still, pg_config remains with the removed version, after postresql > restart and even total reboot: > > $ pg_config > BINDIR = /usr/lib/postgresql/8.4/bin [...] > This creates confusion with external software packages trying to use pgxs, > for example. > Any ideas how to solve this? Use /usr/lib/postgresql/$version/bin/pg_config, and adapt your extension's Makefile to target that. For example with the following in the Makefile: PG_CONFIG ?= pg_config And the call like this from debian/rules or elsewhere: make PG_CONFIG=/usr/lib/postgresql/$version/bin/pg_config I'm preparing some tools to automate all this for debian packaging extensions, see the following: http://lists.alioth.debian.org/pipermail/pkg-postgresql-public/2010-January/000546.html Regards, -- dim -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5137: Upgrade policy clarification
Peter Eisentraut writes: > On sön, 2009-10-25 at 13:52 +, Peter Bengtson wrote: >> It would be good if you to the page describing how a db dump is required >> only for major verson upgrades, e.g. from 8.3.x to 8.4.x but not from, say, >> 8.3.7 to 8.3.8, could add a proviso: a DB dump *is* necessary when upgrading >> from a server compiled for 32 bits even to the same version compiled for 64 >> bit architectures. > > Which page exactly are you talking about? My bet on this one: http://www.postgresql.org/support/versioning -- dim -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5062: 8.4.1 compile fails on debian can't find libdl
Tom Lane writes: > "philip wright" writes: >> Description:8.4.1 compile fails on debian can't find libdl > > Our Debian packager would certainly have complained before now if PG > didn't build on Debian. I suspect that you have forgotten to install > some needed support files. Does Debian have the equivalent of -devel > subpackages? If so you're probably missing whatever -devel package > goes with libdl. debian provides friendly package building tools: http://packages.debian.org/source/sid/postgresql-8.4 # install all build dependancies for postgresql-8.4 apt-get build-dep postgresql-8.4 # build it with debian defaults etc apt-get source -b postgresql-8.4 # or only get the sources apt-get source postgresql-8.4 If you want to tweak the debian options choices, see apt-build. And if the problem is related to building client or server tools for postgresql, then you have the -devel kind of packages: apt-get install postgresql-server-dev-8.4 libpq-dev Regards, -- dim -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4966: wrong password.....
Hi, "walkerlacombe" writes: > Operating system: vista home premium > Description:wrong password. > Details: > > when i boot up my pc i have owner and postgresql as users.for postgresql i > need a password. now when i downloaded postgresql i had to make 2 passwords. > now , when i try to get into the postgresql user it tells me that the > password is the wrong one.i have both of them and they don't work. in other > words i have no access it what so ever. so how do i retrieve thw password > that does work? Would we consider providing a documentation section explaining how to change pg_hba.conf to trust, reload, change password, get back to saner HBA if needed? This question is a such a FAQ that it deserves a place in the docs, I think. Is there a counter-argument (security based or anything) to add this, maybe in the tutorial part, a new Authenticating section between Architectural Fundamentals and Creating a Database? -- dim -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4901: Column name "window" breaks pg_dump/pg_restore
Steve Purcell writes: > # su - postgres -c "env PGCLUSTER=8.3/main pg_dump openx|env > PGCLUSTER=8.4/main pg_restore -d template1 -C" > > And the debian pg_wrapper script ends up selecting the 8.3 pg_dump binary in > the first case. I'll have to figure out if there's even a way to execute > the newer pg_dump against the old database, which I expect to be > fiddly. I guess you could trick the binary choice then force another server choice : pg_dump --cluster 8.4/main -p ... But I'd simply go with: /usr/lib/postgresql/8.4/bin/pg_dump For people unaware of debian way of supporting more than one major version and cluster, all PostgreSQL user binaries in /usr/bin are a link to their pg_wrapper, which support the --cluster 8.X/clustername option and will choose the right versionned binary and port number etc. Regards, -- dim -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] custom_variable_classes module names
Hi, While preparing a module which should make its way to pgfoundry CVS soon (BSD licenced), I stumbled accross a naming problem when using custom_variable_classes. It'll actively refuse the name if it contains an underscore (_): ERROR: unrecognized configuration parameter "pre_prepare.relation" I've resigned to be using the name preprepare.relation, but maybe you'd agree with me that's a bug (documentation doesn't refer to this limitation, but I wouldn't consider this a doc related bug). Regards, -- dim -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] 8.2 pg_freespacemap crash
Hi, We've been hit by a pg_freespacemap contrib module crash (SIGSEGV), gdb showed it was in line 162 of it's pg_freespacemap.c file. fctx->record[i].reltablespace = fsmrel->key.spcNode; Thanks to Andrew Gierth (irc:RhodiumToad) and after some analysis and we've been able to understand what happened: MaxFSMPages is not the maximum number of elements (tables and indexes) contained into the FSM memory, and the contrib believes it is. In fact, AFAUI this number is computed from the max_fsm_pages setting applying to it the 6 bytes per table. When you put in there some index FSM information, you need 4 bytes. So you can store more items than max_fsm_pages * 6 bytes. Please find attached a "stupid" patch for the case not to happen again here, I suppose you'll want some more refined solution to get into -core. Regards, -- dim PS: of course any faulty reasoning here would be mine, and we still have the evidences around (core). diff -Nur -x '*.orig' -x '*~' postgresql-8.2-8.2.11/build-tree/postgresql-8.2.11/contrib/pg_freespacemap/pg_freespacemap.c postgresql-8.2-8.2.11.new/build-tree/postgresql-8.2.11/contrib/pg_freespacemap/pg_freespacemap.c --- postgresql-8.2.11/contrib/pg_freespacemap/pg_freespacemap.c 2006-10-19 20:32:46.0 +0200 +++ postgresql-8.2.11/contrib/pg_freespacemap/pg_freespacemap.c 2009-04-07 12:15:13.0 +0200 @@ -102,7 +102,7 @@ */ FreeSpaceMap = GetFreeSpaceMap(); - numPages = MaxFSMPages; + numPages = MaxFSMPages * 2; funcctx = SRF_FIRSTCALL_INIT(); signature.asc Description: This is a digitally signed message part.
Re: [BUGS] bug found in 8.3 beta 3.
Hi, Le Monday 17 December 2007 19:05:36 chris wood, vous avez écrit : > Hi, I found this bug? In beta 3. I am not a Linux guru so installing any > betas in Debian is very painful for me. Perhaps you could check to see if > it is still there in beta 4?? The experimental sources of debian already provides postgresql-8.3 beta4, so installing it is as easy as adding experimental to your testing server sources.list, or if you want to take no risk of upgrading other experimental packages, adding the deb-src experimental source and backporting: apt-get update apt-get build-dep postgresql-common postgresql-client-common postgresql-8.3 apt-get source -b postgresql-common postgresql-client-common postgresql-8.3 dpkg -i ... http://packages.debian.org/postgresql-8.3 Install at least the -common packages, libpq and the wanted components. Those -common packages contain the infamous debian wrappers which makes debian differs from other PostgreSQL installations, but allows to easily use several version at a time, including binary choice without messing with PATH manually (adding --cluster 8.3/main to "official" options --- version/clustername). Please note that 8.3b2 to 8.3b4 needs an initdb, and the debian packaging has no support for beta to beta upgrading. You'll certainly want to do it manually: pg_dumpall > backup.file apt-get remove postgresql-8.3 rm -rf $PGDATA/* (defaults to /var/lib/postgresql/8.3/main) psql -f backup.file Hope this helps, -- dim signature.asc Description: This is a digitally signed message part.
