Re: [HACKERS] Custom Data Type Question
A simpler way to do this might be to only cache the list per query context. In your IO functions, you could whack a pointer to your cache onto fcinfo-flinfo-fn_extra, and the same flinfo gets passed in for e.g. all output function calls for that column for that query, IIRC. This was what I had in mind originally when I did the enum patch, but I ended up just using syscaches, which I think would be unavailable to you writing a UDT. If my understanding is correct, if fn_extra is null, I would palloc() my data cache and store the pointer in fn_extra? What about freeing this pointer? Or is cleanup automatic? Also, are there any ADTs like a hash-map or tree-map in the server libraries (my background is C++ and am use to having std::map) or do I need to role my own? I am using enumkit for some true enums I have in the DB and like it very much. Though I tend to customize the C-code to optimize it for my use. Thanks, Greg ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Segmentation fault with HEAD.
Rune Bromer [EMAIL PROTECTED] writes: It sure is, but it seems to work now. It could be that the error isn't persistant on a MacBook Pro as these have a x86 arch. Exactly which version of readline (or, perhaps, libedit) are you using? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Custom Data Type Question
As far as memory management goes, do I just use hash_create() and assign that pointer to fn_extra and at the end of the query it will be freed? Or will it not be freed until this end of the transaction? I'm really having trouble understanding the memory management issues with Postgres. Greg Andrew Dunstan wrote: Greg Mitchell wrote: Also, are there any ADTs like a hash-map or tree-map in the server libraries (my background is C++ and am use to having std::map) or do I need to role my own? Look at the dynahash code. I just used it for the first time in a plperl patch, and it's reasonably straightforward. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Custom Data Type Question
Greg Mitchell wrote: Also, are there any ADTs like a hash-map or tree-map in the server libraries (my background is C++ and am use to having std::map) or do I need to role my own? Look at the dynahash code. I just used it for the first time in a plperl patch, and it's reasonably straightforward. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Custom Data Type Question
Greg Mitchell [EMAIL PROTECTED] writes: As far as memory management goes, do I just use hash_create() and assign that pointer to fn_extra and at the end of the query it will be freed? Or will it not be freed until this end of the transaction? I'm really having trouble understanding the memory management issues with Postgres. You have to be careful that the hashtable is created in the correct memory context --- in this case you want it to be in a query-lifespan context, not the short-term (per-tuple-lifespan) context that your function will be called in. The usual procedure for cases like this is to use the context identified by fn_mcxt. src/backend/utils/mmgr/README might make useful reading for you. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] # of tuples on a Table?
Hi I've been making some changes to Postgres insertion method (InsertStmt) and I'm trying to find out how much tuples are stored in one specific table before the insertion of the next tuple, because of that will depend the exact insertion method that I will use... I just really wanna know if there's a implemented sequence for doing that? Thanks... -- Luis D. García M. Telf: 0414-3482018 - FACYT - UC - - Computación -
Re: [HACKERS] [GENERAL] Not your father's question about deadlocks
Clarence Gardner [EMAIL PROTECTED] writes: That scenario seems quite simple, but I can't reproduce the deadlock with this seemingly-identical sequence. This is a bug in 8.1 and up. The reason you couldn't reproduce it is that it requires a minimum of three transactions involved, two of which concurrently grab ShareLock on a tuple --- resulting in a MultiXact being created to represent the concurrent lock holder. The third xact then comes along and tries to update the same tuple, so it naturally blocks waiting for the existing ShareLocks to go away. Then one of the original xacts tries to grab share lock again. It should fall through because it already has the lock, but it fails to recognize this and queues up behind the exclusive locker ... deadlock! Reproducer: Session 1: create table foo (f1 int primary key, f2 text); insert into foo values(1, 'z'); create table bar (f1 int references foo); begin; insert into bar values(1); Session 2: begin; insert into bar values(1); Session 3: update foo set f2='q'; Back to session 1: insert into bar values(1); ERROR: deadlock detected Note that session 2 might actually have exited before the deadlock occurs. I think the problem is that HeapTupleSatisfiesUpdate() always returns HeapTupleBeingUpdated when XMAX is a running MultiXact, even if the MultiXact includes our own transaction. This seems correct for the usages in heap_update and heap_delete --- we have to wait for the multixact's other members to terminate. But in heap_lock_tuple we need a special case when we are already a member of the MultiXact: fall through without trying to reacquire the tuple lock. Comments? Should we change HeapTupleSatisfiesUpdate's API to distinguish this case, or is it better to have a localized change in heap_lock_tuple? regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [GENERAL] Not your father's question about deadlocks
On 11/17/06, Tom Lane [EMAIL PROTECTED] wrote: we need a special case when we are already a member of the MultiXact: fall through without trying to reacquire the tuple lock. Small implementation detail: Also keep a count of how many times the same session requested the same lock, and do not release the lock until he requests same number of releases. This might add (may be significant) overhead, but I am concerned with whether it is desirable? Comments? Should we change HeapTupleSatisfiesUpdate's API to distinguish this case, or is it better to have a localized change in heap_lock_tuple? -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | yahoo }.com
Re: [HACKERS] [GENERAL] Not your father's question about deadlocks
Gurjeet Singh [EMAIL PROTECTED] writes: On 11/17/06, Tom Lane [EMAIL PROTECTED] wrote: we need a special case when we are already a member of the MultiXact: fall through without trying to reacquire the tuple lock. Small implementation detail: Also keep a count of how many times the same session requested the same lock, and do not release the lock until he requests same number of releases. No need for that, because there isn't any heap_unlock_tuple. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [GENERAL] Not your father's question about deadlocks
On 11/17/06, Tom Lane [EMAIL PROTECTED] wrote: Gurjeet Singh [EMAIL PROTECTED] writes: Small implementation detail: Also keep a count of how many times the same session requested the same lock, and do not release the lock until he requests same number of releases. No need for that, because there isn't any heap_unlock_tuple. Cool... I didn't know we could get away with that in PG land!! I assume unlocking is done by a COMMIT/ROLLBACK. -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | yahoo }.com
[HACKERS] A suggestion on PG_TRY() exception handling code.
Hi hackers,
The current usage PG_TRY() looks something like this:
... normal code ...
PG_TRY();
{
... code that might throw ereport(ERROR) ...
}
PG_CATCH();
{
... error recovery code ...
... plus anything that you wish to do even if an error wasn't thrown
...
(because of a PG_RE_THROW possibility)
}
PG_END_TRY();
... do the same thing over again; since either no ERROR or no RE_THROW()
...
... normal code ...
I propose a new constuct, PG_FINALLY. This will help in eliminating code
duplication (hence lesser possibility of errors), and better modularity. It
will also help if someone wishes to call a non-idempotent function in the
now-repeating code.
#define PG_TRY()\
do {\
sigjmp_buf *save_exception_stack = PG_exception_stack; \
ErrorContextCallback *save_context_stack = error_context_stack; \
bool do_re_throw = false; \
sigjmp_buf local_sigjmp_buf;\
if (sigsetjmp(local_sigjmp_buf, 0) == 0)\
{ \
PG_exception_stack = local_sigjmp_buf
#define PG_CATCH() \
} \
else\
{ \
#define PG_FINALLY()\
} \
{ \
PG_exception_stack = save_exception_stack; \
error_context_stack = save_context_stack
#define PG_END_TRY()\
} \
if (do_re_throw)\
siglongjmp(*PG_exception_stack, 1) \
} while (0)
#define PG_RE_THROW() do_re_throw = true
This would change the semantics to:
... normal code ...
PG_TRY();
{
... code that might throw ereport(ERROR) ...
}
PG_CATCH();
{
... (optional) error recovery code only *and/or* RE_THROW...
}
PG_FINALLY();
{
... do something that you wanted done in any case; ERROR or not ...
}
PG_END_TRY();
... normal code ...
Hope to find buyers.
Best regards,
--
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | yahoo }.com
Re: [HACKERS] A suggestion on PG_TRY() exception handling code.
Gurjeet Singh [EMAIL PROTECTED] writes: I propose a new constuct, PG_FINALLY. I took a look through the existing uses of PG_CATCH, and found that in the places where there is duplicate code between the normal and error exits, it's usually just one or two lines. Where it's more than that, it's intermixed with code that is not duplicate, and so PG_FINALLY wouldn't help. I don't care to add code to every use of PG_TRY for such a limited benefit. To the extent that the compiler is able to recognize and optimize out the extra code in blocks where do_re_throw is never changed, that objection loses force --- but in such cases it seems likely that some compilers would throw a warning, which we don't want. Lastly, your proposal silently breaks every existing use of PG_TRY by changing the existing semantics of PG_CATCH. That will certainly not do. (This is not even counting the bug that the CATCH code would be executed inside the same setjmp context, so that any elog within the CATCH code would turn into an infinite loop.) Possibly it'd make sense to offer PG_FINALLY as a mutually exclusive alternative to PG_CATCH, rather than trying to allow both to be used in the same TRY construct. I doubt you've got reasonable semantics for the combination anyway --- it seems at least as likely that the common actions would need to be executed before the special error actions as after. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] replication docs: split single vs. multi-master
Markus Schiltknecht wrote: Not mentioning that categorization doesn't help in clearing the confusion. Just look around, most people use these terms. They're used by MySQL and Oracle. Even Microsofts ActiveDirectory seems to have a multi-master operation mode. OK. For example, Slony is clearly single-master, Agreed. but what about data partitioning? That is multi-master, in that there is more than one master, but only one master per data set. Data Partitioning is a way to work around the trouble of database replication in the application layer. Instead of trying to categorize it like a replication algorithm, we should explain that working around the trouble may be worthwhile in many cases. OK. I am still feeling that data partitioning is like master/slave replication because you have to get that read-only copy to the other server. If you split things up so data sets resided on only one machine, you are right that would not be replication, but do people do that? If so, it is almost another solution. And for multi-master, Oracle RAC is clearly multi master, Yes. and I can see pgpool as multi-master, or as several single-master systems, in that they operate independently. Several single-master systems? C'mon! Pgpool simply implements the most simplistic form of multi-master replication. Just because you can access the single databases inside the cluster doesn't make it less Multi-Master, does it? OK, changed to Multi-Master Replication Using Query Broadcasting. After much thought, it seems that putting things into single/multi-master categories just adds more confusion, because several solutions just aren't clear Agreed, I'm not saying you must categorize all solutions you describe. But please do categorize the ones which can be (and have so often been) categorized. OK. or fall into neither, e.g. Shared Disk Failover. Oh, yes, this reminds me of Brad Nicholson's suggestion in [1] to add a warning about the risk of having two postmaster come up Added. What about other means of sharing disks or filesystems? NBDs or even worse: NFS? Added. Another issue is that you mentioned heavly locking for multi-master, when in fact pgpool doesn't do any special inter-server locking, so it just doesn't apply. Sure it does apply, in the sense that *every* single lock is granted and released on *every* node. The total amount of locks scales linearly with the amount of nodes in the cluster. Uh, but the locks are the same on each machine as if it was a single server, while in a cluster, the locks are more intertwined with other things that are happening on the server, no? In summary, it just seemed clearer to talk about each item and how it works, rather than try to categorize them. The categorization just seems to do more harm than good. Of course, I might be totally wrong, and am still looking for feedback, but these are my current thoughts. Feedback? AFAICT, the categorization in Single- and Multi-Master replication is very common. I think that's partly because it's focused on the solution. One can ask: do I want to write on all nodes or is a failover solution sufficient? Or can I probably get away with a read-only Slave? OK. It's a categorization the user does, often before having a glimpse about how complicated database replication really is. Thus, IMO, it would make sense to help the user and allow him to quickly find answers. (And we can still tell them that it's not easy or even possible to categorize all the solutions.) I didn't mention distributed shared memory as a separate item because I felt it was an implementation detail of clustering, rather than something separate. I kept two-phase in the cluster item for the same reason. Why is pgpool not an implementation detail of clustering, then? Current version at: http://momjian.us/main/writings/pgsql/sgml/failover.html That somehow doesn't work for me: I lost power for a few hours. I am back online. I have updated the docs at that URL. Please check and let me know. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] ALTER TABLE RENAME column
Any reason not to support renaming columns? Can this be added to TODO? patch Description: Binary data -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] ALTER TABLE RENAME column
Jim Nasby [EMAIL PROTECTED] writes: Any reason not to support renaming columns? Can this be added to TODO? Uh, we did that years ago. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] zic data updates
Tom Lane wrote: Joachim Wieland [EMAIL PROTECTED] writes: Current beta seems to have the 2006k version of the zic database. The current zic database is version 2006n however. Since this update does not include source code but just data files I would vote to do the update in beta, what do others think? Sigh, I just updated that six weeks ago ... Tom has done the zic updates to 2006n. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] ALTER TABLE RENAME column
Jim Nasby wrote: Any reason not to support renaming columns? Can this be added to TODO? Upgrade to Postgres95. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Cast null to int4 upgrading from Version 7.2
Jim Nasby wrote: As a less invasive alternative, I *think* you could create an SQL function for casting text to int that treated '' as 0, and then replace the built-in CAST with that. Won't work. You need to replace the data type input function. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] replication docs: split single vs.
Ühel kenal päeval, R, 2006-11-17 kell 00:01, kirjutas Bruce Momjian: Markus Schiltknecht wrote: Not mentioning that categorization doesn't help in clearing the confusion. Just look around, most people use these terms. They're used by MySQL and Oracle. Even Microsofts ActiveDirectory seems to have a multi-master operation mode. OK. For example, Slony is clearly single-master, Agreed. but what about data partitioning? That is multi-master, in that there is more than one master, but only one master per data set. Data Partitioning is a way to work around the trouble of database replication in the application layer. Instead of trying to categorize it like a replication algorithm, we should explain that working around the trouble may be worthwhile in many cases. OK. I am still feeling that data partitioning is like master/slave replication because you have to get that read-only copy to the other server. If you split things up so data sets resided on only one machine, you are right that would not be replication, but do people do that? If so, it is almost another solution. People do that in cases where there is high write loads (high as in not 10+ times less than reads) and just replicating the RO copies would be prohibitively expensive in either network, cpu or memory terms. pl/proxy is one tool for doing it. You can get latest stable version from https://developer.skype.com/SkypeGarage/DbProjects . And for multi-master, Oracle RAC is clearly multi master, Yes. and I can see pgpool as multi-master, or as several single-master systems, in that they operate independently. Several single-master systems? C'mon! Pgpool simply implements the most simplistic form of multi-master replication. In what way is pgpool multimaster ? last time I looked it did nothing but applying DML to several databses. i.e. it is not replication at all, or at least it is masterless, unless we think of the pgpool process itself as the _single_ master :) Just because you can access the single databases inside the cluster doesn't make it less Multi-Master, does it? OK, changed to Multi-Master Replication Using Query Broadcasting. I think this gives completely wrong picture of what pgpool does. How about just Query Broadcasting ? After much thought, it seems that putting things into single/multi-master categories just adds more confusion, because several solutions just aren't clear Agreed, I'm not saying you must categorize all solutions you describe. But please do categorize the ones which can be (and have so often been) categorized. OK. or fall into neither, e.g. Shared Disk Failover. Oh, yes, this reminds me of Brad Nicholson's suggestion in [1] to add a warning about the risk of having two postmaster come up Added. What about other means of sharing disks or filesystems? NBDs or even worse: NFS? Added. Another issue is that you mentioned heavly locking for multi-master, when in fact pgpool doesn't do any special inter-server locking, so it just doesn't apply. Sure it does apply, in the sense that *every* single lock is granted and released on *every* node. The total amount of locks scales linearly with the amount of nodes in the cluster. Uh, but the locks are the same on each machine as if it was a single server, while in a cluster, the locks are more intertwined with other things that are happening on the server, no? In summary, it just seemed clearer to talk about each item and how it works, rather than try to categorize them. The categorization just seems to do more harm than good. Of course, I might be totally wrong, and am still looking for feedback, but these are my current thoughts. Feedback? AFAICT, the categorization in Single- and Multi-Master replication is very common. I think that's partly because it's focused on the solution. One can ask: do I want to write on all nodes or is a failover solution sufficient? Or can I probably get away with a read-only Slave? OK. It's a categorization the user does, often before having a glimpse about how complicated database replication really is. Thus, IMO, it would make sense to help the user and allow him to quickly find answers. (And we can still tell them that it's not easy or even possible to categorize all the solutions.) I didn't mention distributed shared memory as a separate item because I felt it was an implementation detail of clustering, rather than something separate. I kept two-phase in the cluster item for the same reason. Why is pgpool not an implementation detail of clustering, then? Current version at: http://momjian.us/main/writings/pgsql/sgml/failover.html That somehow doesn't work for me: I lost power for a few hours. I am back online. I have updated the docs at that
Re: [HACKERS] [PATCHES] replication docs: split single vs.
Ühel kenal päeval, R, 2006-11-17 kell 00:01, kirjutas Bruce Momjian: Current version at: http://momjian.us/main/writings/pgsql/sgml/failover.html it refers to Warm Standby Using Point-In-Time Recovery (http://momjian.us/main/writings/pgsql/sgml/warm-standby.html), maybe its a good idea to give pointers to SkyTools (description: https://developer.skype.com/SkypeGarage/DbProjects/SkyTools code: http://pgfoundry.org/projects/skytools/ ) which includes a walmgr.py script which sets up and manages WAL-based standby servers. -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
