RE: [HACKERS] New SQL Datatype RECURRINGCHAR
On Sat, 7 Jul 2001, David Bennett wrote: - In a nutshell you are recommending: - create table contact_type ( codeint2, typechar(16), PRIMARY KEY ( code ) ); create table contact ( numberserial, name char(32), type int2, PRIMARY KEY ( number ), FOREIGN KEY ( type ) REFERENCES contact_type ( code ) ); create view contact_with_readble_type as ( select c.number as number, c.name as name, t.type as type from contact c, contact_type t ); * To build a type lookup table: 1) Select type and code from contact_type 2) Build UI object which displays type and returns code Just 'select distinct' on a view should do just fine. * In order to insert a new record with this model: 1) Look up to see if type exists 2) Insert new type 3) Get type ID 4) Insert contact record This can be encapsulated with ON INSERT rule on a view. * The adhoc query user is now faced with the task of understanding 3 data tables. No, only one view. All the logic is encapsulated there. - With recurringchar you could do this easily as: - create table contact ( numberserial, name char(32), type recurringchar1, PRIMARY KEY ( number ), ); * To build a type lookup table: 1) Select distinct type from contact (optimized access to recurringchar dictionary) 2) Build UI object which displays and returns type. * In order to insert a new record with this model: 1) Insert contact record * The adhoc query user has one data table. - Granted, changing the value of contact_type.type would require edits to the contact records. It may be possible to add simple syntax to allow editing of a 'recurringchar dictionary' to get around isolated problem which would only exist in certain applications. Actually, maybe 'dictionary' or 'dictref' would be a better name for the datatype. These things belong in application or middleware (AKA views/triggers), not in database server itself. There are multiple problems with your implementation, for example, transaction handling, assume this situation: Tran A inserts a new contact with new type foo, but does not commit. Dictionary assigns value of N to 'foo'. Tran B inserts a new contact with type foo. What value should be entered in the dictionary? N? A new value? If a type disappears from database, does its dictionary ID get reused? All these questions are not simple questions, and its not up to database to decide it. Your preferred solution belongs in your triggers/views, not in core database. ---(end of broadcast)--- TIP 3: 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] New SQL Datatype RECURRINGCHAR
This would be a potential feature of being able to insert into views in general. Reversing the CREATE VIEW statement to accept inserts, deletes and updates. If true, focus on that. Theres lots of views that cannot be reversed properly -- unions come to mind -- but perhaps this type of simple join could be a first step in the package. I believe this is on the TODO list already. Different attack, but accomplishes the same thing within SQL standards as I seem to recall views are supposed to do this where reasonable. Failing that, implement this type of action the same way as foreign keys. Via the described method with automagically created views, tables, etc. Though I suggest leaving it in contrib for sometime. Enum functionality isn't particularly useful to the majority whose applications tend to pull out the numbers for states when the application is opened (with the assumption they're generally static). -- Rod Taylor Your eyes are weary from staring at the CRT. You feel sleepy. Notice how restful it is to watch the cursor blink. Close your eyes. The opinions stated above are yours. You cannot imagine why you ever felt otherwise. - Original Message - From: Alex Pilosov [EMAIL PROTECTED] To: David Bennett [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Saturday, July 07, 2001 9:24 PM Subject: RE: [HACKERS] New SQL Datatype RECURRINGCHAR On Sat, 7 Jul 2001, David Bennett wrote: - In a nutshell you are recommending: - create table contact_type ( code int2, typechar(16), PRIMARY KEY ( code ) ); create table contact ( number serial, namechar(32), type int2, PRIMARY KEY ( number ), FOREIGN KEY ( type ) REFERENCES contact_type ( code ) ); create view contact_with_readble_type as ( select c.number as number, c.name as name, t.type as type from contact c, contact_type t ); * To build a type lookup table: 1) Select type and code from contact_type 2) Build UI object which displays type and returns code Just 'select distinct' on a view should do just fine. * In order to insert a new record with this model: 1) Look up to see if type exists 2) Insert new type 3) Get type ID 4) Insert contact record This can be encapsulated with ON INSERT rule on a view. * The adhoc query user is now faced with the task of understanding 3 data tables. No, only one view. All the logic is encapsulated there. - With recurringchar you could do this easily as: - create table contact ( number serial, namechar(32), type recurringchar1, PRIMARY KEY ( number ), ); * To build a type lookup table: 1) Select distinct type from contact (optimized access to recurringchar dictionary) 2) Build UI object which displays and returns type. * In order to insert a new record with this model: 1) Insert contact record * The adhoc query user has one data table. - Granted, changing the value of contact_type.type would require edits to the contact records. It may be possible to add simple syntax to allow editing of a 'recurringchar dictionary' to get around isolated problem which would only exist in certain applications. Actually, maybe 'dictionary' or 'dictref' would be a better name for the datatype. These things belong in application or middleware (AKA views/triggers), not in database server itself. There are multiple problems with your implementation, for example, transaction handling, assume this situation: Tran A inserts a new contact with new type foo, but does not commit. Dictionary assigns value of N to 'foo'. Tran B inserts a new contact with type foo. What value should be entered in the dictionary? N? A new value? If a type disappears from database, does its dictionary ID get reused? All these questions are not simple questions, and its not up to database to decide it. Your preferred solution belongs in your triggers/views, not in core database. ---(end of broadcast)--- TIP 3: 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] New SQL Datatype RECURRINGCHAR
On Sat, 7 Jul 2001, Rod Taylor wrote: This would be a potential feature of being able to insert into views in general. Reversing the CREATE VIEW statement to accept inserts, deletes and updates. Definitely not a 'potential' feature, but a existing and documented one. Read up on rules, especially 'ON INSERT DO INSTEAD' stuff. Its not automatic, though. If true, focus on that. Theres lots of views that cannot be reversed properly -- unions come to mind -- but perhaps this type of simple join could be a first step in the package. I believe this is on the TODO list already. On TODO list are updatable views in SQL sense of word, [i.e. automatic updateability of a view which matches certain criteria]. Different attack, but accomplishes the same thing within SQL standards as I seem to recall views are supposed to do this where reasonable. Failing that, implement this type of action the same way as foreign keys. Via the described method with automagically created views, tables, etc. Though I suggest leaving it in contrib for sometime. Enum functionality isn't particularly useful to the majority whose applications tend to pull out the numbers for states when the application is opened (with the assumption they're generally static). Original suggestion was not for an enum type, it was for _dynamically extensible_ data dictionary type. ENUM is statically defined, and it wouldn't be too hard to implement, with one exception: one more type-specific field needs to be added to pg_attribute table, where would be stored argument for the type (such as, length for a char/varchar types, length/precision for numeric type, and possible values for a enum type). This just needs a pronouncement that this addition is a good idea, and then its a trivial thing to implement enum. -alex ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] selecting from cursor
On Mon, 2 Jul 2001, Alex Pilosov wrote:
Erm, forgot to attach the patch. Here it is.
(yow) don't even bother looking at this patch. mail server delayed this
message by almost a week, and by now, the code is totally changed.
I took Tom's suggestion and made RTE a union. So, the below is a new
definition of RTE:
I have most of portal-related code working, only executor needs some more
fixes. Code properly makes PortalScan Path entry, PortalScan Plan nodes,
etc. I have added PortalReScan to tell portal it needs to rescan itself.
I'll post a correct patch next week. Thank you to everyone and especially
Tom for bearing with my often stupid questions.
--cut here--rte definition--
typedef enum RTEType {
RTE_RELATION,
RTE_SUBSELECT,
RTE_PORTAL
} RTEType;
typedef struct RangeTblEntry
{
NodeTag type;
RTEType rtetype;
/*
* Fields valid in all RTEs:
*/
Attr *alias; /* user-written alias clause, if any */
Attr *eref; /* expanded reference names */
boolinh;/* inheritance requested? */
boolinFromCl; /* present in FROM clause */
boolcheckForRead; /* check rel for read access */
boolcheckForWrite; /* check rel for write access */
Oid checkAsUser;/* if not zero, check access as this user
*/
union {
struct {
/* Fields for a plain relation RTE (rtetype=RTE_RELATION) */
char *relname;/* real name of the relation */
Oid relid; /* OID of the relation */
} rel;
struct {
/* Fields for a subquery RTE (rtetype=RTE_SUBSELECT) */
Query *subquery; /* the sub-query */
} sub;
struct {
/* fields for portal RTE (rtetype=RTE_PORTAL) */
char *portalname;/* portal's name */
} portal;
} u;
} RangeTblEntry;
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [HACKERS] Async PQgetResult() question.
Matthew Hagerty [EMAIL PROTECTED] writes: So then how would I code for the exception, i.e. the backend goes down just before or during my call to PQsendQuery()? If I am non-blocking then I can determine that my query did not go (PQsendQuery() or PQflush() returns an error) and attempt to recover. This is the nasty part of any async client, all right. The case of a backend crash doesn't bother me particularly: in the first place, you'll get back a connection closed failure quickly, and in the second place, backend crashes while absorbing query text (as opposed to while executing a query) are just about unheard of. However, the possibility of loss of network connectivity is much more dire: it's plausible, and in most cases you're looking at a very long timeout before the kernel will decide that the connection is toast and report an error to you. I'm unconvinced, however, that using PQsetnonblocking improves the picture very much. Unless the database operations are completely noncritical to what your app is doing, you're going to be pretty much dead in the water anyway with a lost connection :-( In the end you pays your money and you takes your choice. I do recommend reading my past rants about why PQsetnonblocking is broken (circa Jan 2000, IIRC) before you put any faith in it. If you end up deciding that it really is something you gotta have, maybe you'll be the one to do the legwork to make it reliable. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] New SQL Datatype RECURRINGCHAR
This would be a potential feature of being able to insert into views in general. Reversing the CREATE VIEW statement to accept inserts, deletes and updates. Definitely not a 'potential' feature, but a existing and documented one. Read up on rules, especially 'ON INSERT DO INSTEAD' stuff. Its not automatic, though. Trust me, I know how to go about doing those kinds of things manually. I was referring to the automated reveral -- which creates this features in a very simple manner without any additions or changes to system tables -- aside from reverse rules themselves which is a more generic feature. If true, focus on that. Theres lots of views that cannot be reversed properly -- unions come to mind -- but perhaps this type of simple join could be a first step in the package. I believe this is on the TODO list already. On TODO list are updatable views in SQL sense of word, [i.e. automatic updateability of a view which matches certain criteria]. Different attack, but accomplishes the same thing within SQL standards as I seem to recall views are supposed to do this where reasonable. Failing that, implement this type of action the same way as foreign keys. Via the described method with automagically created views, tables, etc. Though I suggest leaving it in contrib for sometime. Enum functionality isn't particularly useful to the majority whose applications tend to pull out the numbers for states when the application is opened (with the assumption they're generally static). Original suggestion was not for an enum type, it was for _dynamically extensible_ data dictionary type. ENUMs from my memory are easily redefined. And since the database they're implemented in requires table locks for everything, they can appear dynamic (nothing is transaction safe in that thing anyway). ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] New SQL Datatype RECURRINGCHAR
This would be a potential feature of being able to insert into views in general. Reversing the CREATE VIEW statement to accept inserts, deletes and updates. Definitely not a 'potential' feature, but a existing and documented one. Read up on rules, especially 'ON INSERT DO INSTEAD' stuff. Its not automatic, though. Trust me, I know how to go about doing those kinds of things manually. I was referring to the automated reveral -- which creates this features in a very simple manner without any additions or changes to system tables -- aside from reverse rules themselves which is a more generic feature. Hmm. My above statement lost all credibility in poor grammer and speeling. Time for bed I suppose. Anyway, the point is that some of the simple views should be straight forward to reversing automatically if someone has the will and the time it can be done. A while back a list of 'views which cannot be reversed' was created and included things such as Unions, Intersections, exclusions, aggregates, CASE statements, and a few more items. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Re: [GENERAL] Vacuum and Transactions
At 05:59 PM 7/6/01 -0400, Bruce Momjian wrote: OK, I just talked to Tom on the phone and here is his idea for 7.2. He says he already posted this, but I missed it. His idea is that in 7.2 VACUUM will only move rows within pages. It will also store unused space locations into shared memory to be used by backends needing to add rows to tables. Actual disk space compaction will be performed by new a VACUUM FULL(?) command. The default VACUUM will not lock the table but only prevent the table from being dropped. Would 7.2 maintain performance when updating a row repeatedly (update, commit)? Right now performance goes down in a somewhat 1/x manner. It's still performs ok but it's nice to have things stay blazingly fast. If not will the new vacuum restore the performance? Or will we have to use the VACUUM FULL? Thanks, Link. ---(end of broadcast)--- TIP 3: 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] Pg on SMP half-powered
On Thursday 05 July 2001 10:51, Víctor Romero wrote: I am running postgresql 7.1 on a SMP Linux box. It runs, but it never pass a loadavg of 0.4, no matter how I try to overload the system. The same configuration, the same executable, the same test on a non-SMP machine gives a loadavg of 19. Sounds like a kernel issue. However, the load average numbers alone are not enough information to get a benchmark. You need to benchmark using a benchmark that can generate enough traffic to load both machines and get good time results for the run of the standard benchmark queries. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 3: 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] 2 gig file size limit
On Friday 06 July 2001 18:51, Naomi Walker wrote: If PostgreSQL is run on a system that has a file size limit (2 gig?), where might cause us to hit the limit? Since PostgreSQL automatically segments its internal data files to get around such limits, the only place you will hit this limit will be when making backups using pg_dump or pg_dumpall. You may need to pipe the output of those commands into a file splitting utility, and then you'll have to pipe through a reassembly utility to restore. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
