Re: [SQL] surrogate key or not?
Markus, Iain, (B (B> Thus I see it more as an issue of business logic than performance. There are (B> of course many other considerations with relational theory and stuff like (B> that which you could debate endlessly. I expect that googling on "surrogate (B> keys" would yeild interesting results. (B (BFrankly, I couldn't disagree more. This is why it was so problematic for the (BSQL committee to enshrine "primary keys" and sequences in the standard; it (Bmis-educates database designers into believing that surrogate keys are (Bsomehow part of the data model. They are most decidely NOT. (B (BGiven: Surrogate keys, by definition, represent no real data; (BGiven: Only items which represent real data have any place in (Ba data model (BConclusion: Surrogate keys have no place in the data model (B (BThere are, in fact, three very good reasons to use surrogate keys, all of (Bwhich are strictly due to limitations of technology; that is, implementation (Band performance issues, NOT business logic. They are: (B (B1) Convenience: It's very annoying to have to refer to a 4-column foriegn key (Bwhenever you do a join in queries or want to delete a record, as well as (Btracking a 4-element composite in your client software. (B (B2) Performance: INT and BIGINT data types are among the most compact and (Befficient stored in most RDBMSs. So using anything else as a key would (Blikely result in a loss of performance on large-table joins. (B (B3) Mutability: Most RDBMSs are very inefficient about CASCADE deletes and (Bupdates. Some RDBMSs do not support CASCADE, forcing the client software to (Bfix all the dependant rows. This means that DBAs are very reluctant to use (Bcolumns which change frequently as join keys. (B (BAll three of these implementation issues are, at least in theory, (Bsurmountable. For example, Sybase overcame problems (1) and (3) by creating (Ban automated, system-controlled hash key based on the table's real key. This (Bwas a solution endorsed by E.F. Codd in the mid-90's when he came to regret (Bhis promotion of the "Primary Key" idea in the SQL standard. (B (BNow, you're probably wondering "why does this guy regard surrogate keys as a (Bproblem?" I'll tell you: I absolutely cannot count the number of "bad (Bdatabases" I've encountered which contained tables with a surrogate key, and (BNO REAL KEY of any kind. This makes data normalization impossible, and (Bcleanup of the database becomes a labor-intensive process requiring (Bhand-examination of each row. (B (B-- (B-Josh Berkus (B Aglio Database Solutions (B San Francisco (B (B (B---(end of broadcast)--- (BTIP 9: the planner will ignore your desire to choose an index scan if your (B joining column's datatypes do not match
Re: [SQL] locks and triggers. give me an advice please
thnx. i try to sound the idea to ensure myself that you are right. > begin transaction; > lock t1 in access exclusive mode; > > Turn off triggers and do your updates. > (Note, "truncate t1" is faster than "delete from t1" followed by a > "vacuum full" and you might consider running "reindex table t1" after > your mass update or if appropriate drop your indexes, load the data, > then recreate them.) > > Re-establish triggers. > > commit; --end of transaction unlocks the table in case another user inserts a record into an adjuscent table with the value of reference field NOT IN t1. the constraint causes reading of t1 to look up FK value IN t1. so my EXCLUSIVE lock prevents even reading and this user operation will be queued. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Inherited tables and new fields
On Tue, 2004-07-20 at 15:36, Jeff Boes wrote: ... > Of course, had we used table inheritance, we'd do something like ... > >select * from draft_template ... > > but it wouldn't do exactly what we are doing now: that is, > fn_all_drafts() returns not only the contents of every row in the tables > draft_X, but also an extra column indicating which table that row > came from. You can do that with an inheritance hierarchy like this: select tableoid::regclass as tablename, * from my_table; >create table all_drafts (editor_id integer) inherits draft_template; > > What frustrates me from time to time is that if "draft_template" is > altered to add a new column, then the function breaks because the new > column appears in "all_drafts" as *following* editor_id. The column > order messes up the code in the function, because it's expecting > all_drafts to look like draft_template, with editor_id added at the end. > > Is this a mis-feature? New columns get added at the end of each table; that is standard. -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA "Greater love hath no man than this, that a man lay down his life for his friends." John 15:13 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] surrogate key or not?
Markus, Oh, so you want USEFUL answers. OK. > Now when I want to search for a type in types or another table that > references types(type_id), under what circumstances is it advisable to > have a surrogate integer key and not use the unique type name? When using the actual name will be a performance problem. > Is > searching for an integer as fast as is searching for a string when both > have an index? Not usually, no. The index on the text values will simply be larger than the one on 4-byte INTs, which means it's "slower", assuming you run out of memory some of the time. If your whole DB fits in RAM, it's not worth worrying about. > How many records in the type table do I need to make a > surrogate key a not unsignificantly faster way to retrieve a row? It needs to be large enougth that the difference in data types makes a difference in whether or not it will fit into sort_mem, and how likely it is to be already cached in memory. > What > about joins? Double jeopardy; you're using the column twice so double the storage difference. Otherwise, it's just the same issue; does it still fit in RAM or not? > Are these the right questions? Also you'll want to consider the speed of CASCADE operations whenever a type_name changes. If these changes occur extremely infrequently, then you can ignore this as well. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Please help me.. problem in to_char
Hi,
I want convert from mysql to postresql, previously
in mysql the code as below:
SELECT t2.id, t2.name, date_format(t1.created,\'%W %M %e, %Y - %r\')
In postresql no date_format function, we need to use to_char function
but it still work because still need to put ::date such as
SELECT to_char('2005-03-27'::date,'DD/MM/');
How can i put ::date beside t1.created to get the output?
Any idea??
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] surrogate key or not?
Thnx, Josh (Byou are very helpful. (B (B> There are, in fact, three very good reasons to use surrogate keys, all of (B> which are strictly due to limitations of technology; that is, (B> implementation and performance issues, NOT business logic. They are: (B> (B> 1) Convenience: It's very annoying to have to refer to a 4-column foriegn (B> key whenever you do a join in queries or want to delete a record, as well (B> as tracking a 4-element composite in your client software. (B> (B> 2) Performance: INT and BIGINT data types are among the most compact and (B> efficient stored in most RDBMSs. So using anything else as a key would (B> likely result in a loss of performance on large-table joins. (B> (B> 3) Mutability: Most RDBMSs are very inefficient about CASCADE deletes and (B> updates. Some RDBMSs do not support CASCADE, forcing the client software (B> to fix all the dependant rows. This means that DBAs are very reluctant to (B> use columns which change frequently as join keys. (B (B> Now, you're probably wondering "why does this guy regard surrogate keys as (B> a problem?" I'll tell you: I absolutely cannot count the number of "bad (B> databases" I've encountered which contained tables with a surrogate key, (B> and NO REAL KEY of any kind. This makes data normalization impossible, (B> and cleanup of the database becomes a labor-intensive process requiring (B> hand-examination of each row. (B (B (BThe surrogate keys is a real big problem. (BBut i want to add another two reasons to have a surrogate keys (B (B4) Replication: to identify an object of ANY type (record of any table (Bregardless to datamodel), to store lists of deleted or modified objects (B(regardless to datamodel) (B (B5) Making a primary key: if there is no real key at all. (B (Bthe sentence (5) is debatable. in theory every relation has a real key, but in (Bthe practice we have historical datamodels without PK !!! it is impossible (Bbut it exists. (BFor example: (BHere in Russia we have a lot of different but identically named streets within (Bone city. They has absoluetly identical attributes. Historically only human (Bnot machines work on that datamodel and they all used to call such streets by (Bname adding some spechial non-formal explainations, for example: (B"deliver this message please to the house 35 on the Green street, that is to (Bthe west of the center of the city." (B"deliver this message please to the house 12 on the Green street, that is (Bshortest of the all Green streets in the town." (BAnother exaple is a table of user messages: (Buser has a number of messages they have no mean attribute and also could be (Bidentical, so you force this user to numbering his messages, or number them (Byourself. (B (B (B (B---(end of broadcast)--- (BTIP 4: Don't 'kill -9' the postmaster
Re: [SQL] surrogate key or not?
Ð ÐÑÐ, 21.07.2004, Ð 09:44, sad ÐÐÑÐÑ: > 4) Replication: to identify an object of ANY type (record of any table > regardless to datamodel), to store lists of deleted or modified objects > (regardless to datamodel) That sounds like a datamodel on data that belongs to another data model, and 5) applies. > 5) Making a primary key: if there is no real key at all. When there's no key at all, there can't be a surrogate key, as I understand it. In such cases a generated unique number comes in handy, and it's a real primary key and no surrogate key. ÐÐÐÑÐÐÐ -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] surrogate key or not?
On Wednesday 21 July 2004 14:58, Markus Bertheau wrote: > > 4) Replication: to identify an object of ANY type (record of any table > > regardless to datamodel), to store lists of deleted or modified objects > > (regardless to datamodel) > > That sounds like a datamodel on data that belongs to another data model, that's right, these surr. keys actually belong to a metadata model, but it is also useful to references of a data model. and they are used this way. > > 5) Making a primary key: if there is no real key at all. > > When there's no key at all, there can't be a surrogate key, as I > understand it. In such cases a generated unique number comes in handy, > and it's a real primary key and no surrogate key. ...right. in case (5) we construct another datamodel indeed. with an artifichial real key :-) P.S. r u from Russia ? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Please help me.. problem in to_char
Ð ÐÑÐ, 21.07.2004, Ð 10:33, azah azah ÐÐÑÐÑ:
> Hi,
> I want convert from mysql to postresql, previously
> in mysql the code as below:
>
> SELECT t2.id, t2.name, date_format(t1.created,\'%W %M %e, %Y - %r\')
>
> In postresql no date_format function, we need to use to_char function
> but it still work because still need to put ::date such as
> SELECT to_char('2005-03-27'::date,'DD/MM/');
>
> How can i put ::date beside t1.created to get the output?
Just do it:
to_char(t1.created::date, 'DD/MM/')
--
Markus Bertheau <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
[SQL] ? on announcement of 7.5
Hello can anyone comment the announcement of 7.5 about "nested transactions" ? doesn't the nesting hurt the matter of transaction ? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Please help me.. problem in to_char
what kind of column is t1.created? It appears that it
is a text column and the format looks like a date. Is
this correct or is it a date? I need more information
about your table structure.
What about:
SELECT t2.id, t2.name, to_char(cast (t1.created as
date),'DD/MM/')
but the other '::' should work also.
extremedb=> SELECT to_char(cast ('12/4/2004' as
date),'DD/MM/');
to_char
04/12/2004
(1 row)
extremedb=> SELECT
to_char('12/4/2004'::date,'DD/MM/');
to_char
04/12/2004
(1 row)
I have a table with a timestamp in it called
createdate..
\d clientinfo
Table "public.clientinfo"
Column | Type |
Modifiers
-+--+-
acode| text|
not null
createdate | timestamp with time zone | default
now()
extremedb=> SELECT to_char(cast (clientinfo.createdate
as date),'DD/MM/') from clientinfo;
to_char
14/07/2004
14/07/2004
14/07/2004
Ted
--- azah azah <[EMAIL PROTECTED]> wrote:
> Hi,
> I want convert from mysql to postresql, previously
> in mysql the code as below:
>
> SELECT t2.id, t2.name, date_format(t1.created,\'%W
> %M %e, %Y - %r\')
>
> In postresql no date_format function, we need to use
> to_char function
> but it still work because still need to put ::date
> such as
> SELECT to_char('2005-03-27'::date,'DD/MM/');
>
> How can i put ::date beside t1.created to get the
> output?
> Any idea??
>
> ---(end of
> broadcast)---
> TIP 1: subscribe and unsubscribe commands go to
> [EMAIL PROTECTED]
>
__
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] date_format in postresql
Check your version with:
select version();
In the meantime, you could try
date_part('epoch', submittime)
which will return a unix timestamp, you could then use functions within
your scripting language to convert to human readable date formats.
Frank
At 11:53 PM 7/20/04, azah azah wrote:
Thanks Chris and Rod.
I think I have a problem because not many function existing in
postresql that i installed.
How can i get all the build-in functions because the basic function
to_char is not existing in the database?
On Tue, 20 Jul 2004 22:39:38 -0400, Rod Taylor <[EMAIL PROTECTED]> wrote:
> On Tue, 2004-07-20 at 20:55, azah azah wrote:
> > Hi,
> > I want convert from mysql to postresql,
> > in mysql, query to database using the code as below:
> >
> > date_format(submittime, "%W %M %e, %Y - %r")
>
> to_char(submittime, 'format string')
>
> http://www.postgresql.org/docs/7.4/static/functions-formatting.html
>
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] immutable function calling stable function
Hi, shouldn't it be illegal for an immutable function to call a stable one? -- Markus Bertheau <[EMAIL PROTECTED]> ---(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: [SQL] date_format in postresql
On Tue, 2004-07-20 at 23:53, azah azah wrote: > Thanks Chris and Rod. > > I think I have a problem because not many function existing in > postresql that i installed. > How can i get all the build-in functions because the basic function > to_char is not existing in the database? Which version of PostgreSQL are you using? select version(); > On Tue, 20 Jul 2004 22:39:38 -0400, Rod Taylor <[EMAIL PROTECTED]> wrote: > > On Tue, 2004-07-20 at 20:55, azah azah wrote: > > > Hi, > > > I want convert from mysql to postresql, > > > in mysql, query to database using the code as below: > > > > > > date_format(submittime, "%W %M %e, %Y - %r") > > > > to_char(submittime, 'format string') > > > > http://www.postgresql.org/docs/7.4/static/functions-formatting.html > > > > > > ---(end of broadcast)--- > > TIP 9: the planner will ignore your desire to choose an index scan if your > > joining column's datatypes do not match > > ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] immutable function calling stable function
On Wed, 21 Jul 2004, Markus Bertheau wrote: > shouldn't it be illegal for an immutable function to call a stable one? It's expected that the function's creator is responsible for properly marking its stability. This allows some flexibility when you know more than the system does (for example, a stable function that isn't immutable because of its use of a GUC variable being called from a function that sets said variable). ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] MySQL-style "create temporary table foo select ..."
Hi, I've got some code that currently uses MySQL, and it copies an existing table into a temporary one like so: create temporary table tmp_foo select * from foo; That creates 'tmp_foo' with the same columns as 'foo', which is very convenient. I can't seem to find any reference to how to do that with Postgres (and I'd like if possible to ditch MySQL). Just wondering if anyone has ideas about how to do this? I'd like to avoid having to work up a table definition to define the temp table's columns. Or maybe I could do some sort of trick with the system catalog tables to get the column information in a convenient form? cheers, geoff -- Geoff Richards - GBdirect - http://development.gbdirect.co.uk/ tel: 0870 200 7273 sales: 0800 651 0338 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] immutable function calling stable function
Markus Bertheau <[EMAIL PROTECTED]> writes: > shouldn't it be illegal for an immutable function to call a stable one? No, because that's the standard hack for getting around it when you wish to call a stable function in what you know to be a "safe" way. If you look in the archives you'll find cases where creating an immutable wrapper function was the recommended solution to performance problems. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] MySQL-style "create temporary table foo select ..."
create temporary table tmp_foo AS select * from foo; Geoff Richards schrieb: Hi, I've got some code that currently uses MySQL, and it copies an existing table into a temporary one like so: create temporary table tmp_foo select * from foo; That creates 'tmp_foo' with the same columns as 'foo', which is very convenient. I can't seem to find any reference to how to do that with Postgres (and I'd like if possible to ditch MySQL). Just wondering if anyone has ideas about how to do this? I'd like to avoid having to work up a table definition to define the temp table's columns. Or maybe I could do some sort of trick with the system catalog tables to get the column information in a convenient form? cheers, geoff ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Sorry too many conecctions
hi everybody I speak english a little,Sorry. I have A problem whith de connections from a Aplicatio Java, guive me a Error "Sorry too many clients",I think what is becuase I don't close connecction to the DB postgresql, I do aprox. 1000 conecction into statement "while" What can Do? thakns _ Charla con tus amigos en línea mediante MSN Messenger: http://messenger.latam.msn.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] surrogate key or not?
> All three of these implementation issues are, at least in theory, > surmountable. For example, Sybase overcame problems (1) and (3) by creating > an automated, system-controlled hash key based on the table's real key. This > was a solution endorsed by E.F. Codd in the mid-90's when he came to regret > his promotion of the "Primary Key" idea in the SQL standard. I've often wondered if the OID was intended to be something similar. You specify: CREATE TABLE abc (key varchar(120) PRIMARY KEY); CREATE TABLE bcd (key varchar(120) REFERENCES abc); PostgreSQL was actually supposed to create: abc(oid oid, key varchar(120) PRIMARY KEY); bcd(key oid REFERENCES abc(oid)); Then muck around with the queries to resolve the actual varchar() name when required. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] MySQL-style "create temporary table foo select ..."
On Wed, Jul 21, 2004 at 05:07:46PM +0200, Michael Kleiser wrote: > create temporary table tmp_foo AS select * from foo; Ah, that's a relief, thanks. Just what I need. Must have missed it in the create table syntax. > Geoff Richards schrieb: > >Hi, > > > >I've got some code that currently uses MySQL, and it copies an existing > >table into a temporary one like so: > > > > create temporary table tmp_foo select * from foo; > > > >That creates 'tmp_foo' with the same columns as 'foo', which is > >very convenient. I can't seem to find any reference to how to do > >that with Postgres (and I'd like if possible to ditch MySQL). > > > >Just wondering if anyone has ideas about how to do this? I'd like > >to avoid having to work up a table definition to define the temp > >table's columns. > > > >Or maybe I could do some sort of trick with the system catalog tables > >to get the column information in a convenient form? > > > > cheers, > > geoff > > > -- Geoff Richards - GBdirect - http://development.gbdirect.co.uk/ tel: 0870 200 7273 sales: 0800 651 0338 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] surrogate key or not?
Sad, (B (BFirst of all, please excuse me if I've misunderstood you below because of (Btranslation issues. You'll find I'm rather strident, but it's because the (Breasons you're presenting, or seem to be, are excuses for bad database design (BI hear every day on the job, and end up having to fix when they go wrong. (B (BNow, to reply to your comments: (B (B> 4) Replication: to identify an object of ANY type (record of any table (B> regardless to datamodel), to store lists of deleted or modified objects (B> (regardless to datamodel) (B (BOnly if the Replication product requires it. Note that our new replication (Bengine, Slony-I, does *not* require surrogate keys; it can handle (Bmulti-column primary keys. I'm not sure about the other replication (Bproducts, they may be able to as well. (B (BI think what you're talking about is a GUID (Global Unique Identifier). Once (Bagain, while most GUIDs are based on random number theory, the ideal GUID (Bwould be a unique combination of the Row Key, the version, and the server (Bidentification -- making it "real data" instead of a random number that tells (Byou nothing about the row. For performance, this combination might be (Bcombined in a hash. (B (BIn other words, I think your 4th point is actually part of Point (1), (Bconvenience. (B (B> 5) Making a primary key: if there is no real key at all. (B (BNo, no, no, a thousand times NO. (B (BData without a real key is NOT DATA. It is just garbage. (B (BI manage or consult on more than twenty production databases for my clients. (BEvery single one of those databases has a real unique key on every single (Btable. The ONLY exception is "holding tables" for incoming bulk data, some (Bof which will be retained as data and some of which will discarded as garbage (B-- and key uniqueness is the primary test of what is and isn't data. (B (B> the sentence (5) is debatable. in theory every relation has a real key, but (B> in the practice we have historical datamodels without PK !!! it is (B> impossible but it exists. (B (BOnly because you have bad historical databases. This is not an excuse, it is (Ba reason to fix them. (B (B> For example: (B> Here in Russia we have a lot of different but identically named streets (B> within one city. They has absoluetly identical attributes. Historically (B> only human not machines work on that datamodel and they all used to call (B> such streets by name adding some spechial non-formal explainations, for (B> example: (B> "deliver this message please to the house 35 on the Green street, that is (B> to the west of the center of the city." (B> "deliver this message please to the house 12 on the Green street, that is (B> shortest of the all Green streets in the town." (B (BThis is a PERFECT example of the evil of surrogate keys. You had a data (Bproblem, and instead of solving it, you chose to lean on surrogate keys like (Ba crutch. Here's what you have in your database now: (B (BStreets (BID Street Name (B345 Green Street (B2019Green Street (B5781Green Street (BKey: ID??? (B (BHow, exactly, do you expect to distinguish the above 3 "green streets" from (Beach other? How do you expect to your users to know that 345 is West Green (BStreet and 5781 is the shortest in town? How are they supposed to choose (Bbetween the three? How do you know there are actually 3 and maybe not just (Btwo or even one? The ID 5781 isn't exactly informative; in fact, it's (Bmeaningless. It's GARBAGE. (B (BLook, real data, on a row-by-row basis, is a POSTULATE. It is should (Bdescribe adequately the portion of the world you are trying to represent. (BAutomated surrogate keys are NOT part of these postulates since they convey (Bno meaningful information. This is what you currently have: (B (BThere is a street named GREEN STREET. (BThere is a street named GREEN STREET. (BThere is a street named GREEN STREET. (B (BAs you can see by rendering it in English, those three statements are damned (Bconfusing. Are there three streets named Green Street, or is there only (Bone? Either could be true. We don't know, the data doesn't tell us. (B (BWhat you need is: (B (BStreets (BID Street Name Location (B345 Green StreetWest Side of City (B2019Green StreetIn Front of Consulate (B5781Green StreetShortest in Town (BKey: Street Name, Location (B (BThis gives you much more meaningful data: (B (BThere is a street named GREEN STREET which is on the WEST SIDE OF THE CITY. (BThere is a street named GREEN STREET which is IN FRONT OF THE CONSULATE. (BThere is a street named GREEN STREET which is the SHORTEST IN TOWN. (B (BThis tells the user which Green Street he wants to select.It also gives us (Ba fair indication that there are 3 Green Streets, and not one or two. And if (Bthere is duplication -- for example
Re: [SQL] Sorry too many conecctions
In the root directory in which postgres stores the data is a file: postgresql.conf Edit the file with a text editor (vi / ed / etc.) and change : max_connections = ### to max_connections = 1000 (or more since other applications or a DBA may need to connect as well) and also change shared_buffers = ### to be at least (max_connections * 2), so change this to at least 2000 for 1000 simulataneous connections. More connections use more shared memory so you want to have some limits to the total number. Save the file changes. Stop and restart the postgres instance (reload doesn't work for these settings). Hope this helps, Greg Williamson DBA GlobeXplorer LLC -Original Message- From: Javier Tesis Tolosa Trabajo [mailto:[EMAIL PROTECTED] Sent: Wed 7/21/2004 8:08 AM To: [EMAIL PROTECTED] Cc: Subject:[SQL] Sorry too many conecctions hi everybody I speak english a little,Sorry. I have A problem whith de connections from a Aplicatio Java, guive me a Error "Sorry too many clients",I think what is becuase I don't close connecction to the DB postgresql, I do aprox. 1000 conecction into statement "while" What can Do? thakns _ Charla con tus amigos en línea mediante MSN Messenger: http://messenger.latam.msn.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] ? on announcement of 7.5
Sad, > can anyone comment the announcement of 7.5 > about "nested transactions" ? > doesn't the nesting hurt the matter of transaction ? 7.5 hasn't been announced. It's not even in beta. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] surrogate key or not?
Hi Josh, (B (BConsidering that I generally agree with your comments (in this and your (Blater posts) (BI'd say I didn't make myself clear in my brief comment. (B (BOf the considerations: performance, convenience and business logic, I (Bpersonally rate performance as the lowest priority. The convenience part has (Bbeen well covered in other posts and is second most in my opinion.. (B (BBy business logic I was thinking of a situation we had on an old db where (Bemployees were idenitfied by codes. These codes were transmitted all over (Bthe database (as you can imagine) which meant that the codes couldn't be (Brecycled as employees came and left. The answer was to introduce an integer (Bbased auto-generated key. That way the data associated with an ex-employee (Bis associated with that employee, not with the code. It may be that this (Breally a convenience issue as opposed to business logic, but in my thinking (Bthe code was data, not a (Bprimary key - it is not even an alternate key (if ex-employees have their (Bcode (Bnulled it can be made "unique" though). Perhaps the term surrogate key (Bdoesn't apply in this case, I have to admit that I'm not 100% on terminalogy (Bhere. (B (BIn the end, accurately representing the business logic is most importent, (Band use of a contrived sequential key shouldnt preclude the use of unique (Bconstraints where needed so the problem you described of duplicate events (Bshould never have happened anyway. (B (BThis is a great topic though, I find it really interesting. (B (BRegards (BIain (B (B (B (B (B- Original Message - (BFrom: "Josh Berkus" <[EMAIL PROTECTED]> (BTo: "Iain" <[EMAIL PROTECTED]>; "Markus Bertheau" <[EMAIL PROTECTED]>; (B<[EMAIL PROTECTED]> (BSent: Wednesday, July 21, 2004 4:00 PM (BSubject: Re: [SQL] surrogate key or not? (B (B (B> Markus, Iain, (B> (B> > Thus I see it more as an issue of business logic than performance. There (Bare (B> > of course many other considerations with relational theory and stuff (Blike (B> > that which you could debate endlessly. I expect that googling on (B"surrogate (B> > keys" would yeild interesting results. (B> (B> Frankly, I couldn't disagree more. This is why it was so problematic for (Bthe (B> SQL committee to enshrine "primary keys" and sequences in the standard; it (B> mis-educates database designers into believing that surrogate keys are (B> somehow part of the data model. They are most decidely NOT. (B> (B> Given: Surrogate keys, by definition, represent no real data; (B> Given: Only items which represent real data have any place in (B> a data model (B> Conclusion: Surrogate keys have no place in the data model (B> (B> There are, in fact, three very good reasons to use surrogate keys, all of (B> which are strictly due to limitations of technology; that is, (Bimplementation (B> and performance issues, NOT business logic. They are: (B> (B> 1) Convenience: It's very annoying to have to refer to a 4-column foriegn (Bkey (B> whenever you do a join in queries or want to delete a record, as well as (B> tracking a 4-element composite in your client software. (B> (B> 2) Performance: INT and BIGINT data types are among the most compact and (B> efficient stored in most RDBMSs. So using anything else as a key would (B> likely result in a loss of performance on large-table joins. (B> (B> 3) Mutability: Most RDBMSs are very inefficient about CASCADE deletes and (B> updates. Some RDBMSs do not support CASCADE, forcing the client software (Bto (B> fix all the dependant rows. This means that DBAs are very reluctant to (Buse (B> columns which change frequently as join keys. (B> (B> All three of these implementation issues are, at least in theory, (B> surmountable. For example, Sybase overcame problems (1) and (3) by (Bcreating (B> an automated, system-controlled hash key based on the table's real key. (BThis (B> was a solution endorsed by E.F. Codd in the mid-90's when he came to (Bregret (B> his promotion of the "Primary Key" idea in the SQL standard. (B> (B> Now, you're probably wondering "why does this guy regard surrogate keys as (Ba (B> problem?" I'll tell you: I absolutely cannot count the number of "bad (B> databases" I've encountered which contained tables with a surrogate key, (Band (B> NO REAL KEY of any kind. This makes data normalization impossible, and (B> cleanup of the database becomes a labor-intensive process requiring (B> hand-examination of each row. (B> (B> -- (B> -Josh Berkus (B> Aglio Database Solutions (B> San Francisco (B (B (B---(end of broadcast)--- (BTIP 7: don't forget to increase your free space map settings
Re: [SQL] Please help me.. problem in to_char
Why still not working???
I have try all the suggestions, still error like below:
ERROR: cannot cast type character varying to date
I'm using latest version of postresql.
On Wed, 21 Jul 2004 05:35:06 -0700 (PDT), Theodore Petrosky
<[EMAIL PROTECTED]> wrote:
>
> what kind of column is t1.created? It appears that it
> is a text column and the format looks like a date. Is
> this correct or is it a date? I need more information
> about your table structure.
>
> What about:
>
> SELECT t2.id, t2.name, to_char(cast (t1.created as
> date),'DD/MM/')
>
> but the other '::' should work also.
>
> extremedb=> SELECT to_char(cast ('12/4/2004' as
> date),'DD/MM/');
> to_char
>
> 04/12/2004
> (1 row)
>
> extremedb=> SELECT
> to_char('12/4/2004'::date,'DD/MM/');
> to_char
>
> 04/12/2004
> (1 row)
>
> I have a table with a timestamp in it called
> createdate..
>
> \d clientinfo
> Table "public.clientinfo"
> Column | Type |
> Modifiers
> -+--+-
> acode| text|
> not null
> createdate | timestamp with time zone | default
> now()
>
> extremedb=> SELECT to_char(cast (clientinfo.createdate
> as date),'DD/MM/') from clientinfo;
> to_char
>
> 14/07/2004
> 14/07/2004
> 14/07/2004
>
> Ted
>
> --- azah azah <[EMAIL PROTECTED]> wrote:
> > Hi,
> > I want convert from mysql to postresql, previously
> > in mysql the code as below:
> >
> > SELECT t2.id, t2.name, date_format(t1.created,\'%W
> > %M %e, %Y - %r\')
> >
> > In postresql no date_format function, we need to use
> > to_char function
> > but it still work because still need to put ::date
> > such as
> > SELECT to_char('2005-03-27'::date,'DD/MM/');
> >
> > How can i put ::date beside t1.created to get the
> > output?
> > Any idea??
> >
> > ---(end of
> > broadcast)---
> > TIP 1: subscribe and unsubscribe commands go to
> > [EMAIL PROTECTED]
> >
>
>
> __
> Do you Yahoo!?
> Yahoo! Mail - 50x more storage than other providers!
> http://promotions.yahoo.com/new_mail
>
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Please help me.. problem in to_char
Thanks all, :)
It working now, i'm using code as below:
to_char(t1.created::date,'DD/MM/')
but other problem come out, error as below:
ERROR: relation "plugins" does not exist
what that's mean?? table plugins already exists..
On Thu, 22 Jul 2004 10:43:35 +0800, azah azah <[EMAIL PROTECTED]> wrote:
> Why still not working???
> I have try all the suggestions, still error like below:
> ERROR: cannot cast type character varying to date
> I'm using latest version of postresql.
>
>
>
> On Wed, 21 Jul 2004 05:35:06 -0700 (PDT), Theodore Petrosky
> <[EMAIL PROTECTED]> wrote:
> >
> > what kind of column is t1.created? It appears that it
> > is a text column and the format looks like a date. Is
> > this correct or is it a date? I need more information
> > about your table structure.
> >
> > What about:
> >
> > SELECT t2.id, t2.name, to_char(cast (t1.created as
> > date),'DD/MM/')
> >
> > but the other '::' should work also.
> >
> > extremedb=> SELECT to_char(cast ('12/4/2004' as
> > date),'DD/MM/');
> > to_char
> >
> > 04/12/2004
> > (1 row)
> >
> > extremedb=> SELECT
> > to_char('12/4/2004'::date,'DD/MM/');
> > to_char
> >
> > 04/12/2004
> > (1 row)
> >
> > I have a table with a timestamp in it called
> > createdate..
> >
> > \d clientinfo
> > Table "public.clientinfo"
> > Column | Type |
> > Modifiers
> > -+--+-
> > acode| text|
> > not null
> > createdate | timestamp with time zone | default
> > now()
> >
> > extremedb=> SELECT to_char(cast (clientinfo.createdate
> > as date),'DD/MM/') from clientinfo;
> > to_char
> >
> > 14/07/2004
> > 14/07/2004
> > 14/07/2004
> >
> > Ted
> >
> > --- azah azah <[EMAIL PROTECTED]> wrote:
> > > Hi,
> > > I want convert from mysql to postresql, previously
> > > in mysql the code as below:
> > >
> > > SELECT t2.id, t2.name, date_format(t1.created,\'%W
> > > %M %e, %Y - %r\')
> > >
> > > In postresql no date_format function, we need to use
> > > to_char function
> > > but it still work because still need to put ::date
> > > such as
> > > SELECT to_char('2005-03-27'::date,'DD/MM/');
> > >
> > > How can i put ::date beside t1.created to get the
> > > output?
> > > Any idea??
> > >
> > > ---(end of
> > > broadcast)---
> > > TIP 1: subscribe and unsubscribe commands go to
> > > [EMAIL PROTECTED]
> > >
> >
> >
> > __
> > Do you Yahoo!?
> > Yahoo! Mail - 50x more storage than other providers!
> > http://promotions.yahoo.com/new_mail
> >
> >
> >
> > ---(end of broadcast)---
> > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
> >
>
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] date_format in postresql
Hi all, Successful solved. I'm just using to_timestamp function. Thanks On Wed, 21 Jul 2004 10:45:27 -0400, Rod Taylor <[EMAIL PROTECTED]> wrote: > On Tue, 2004-07-20 at 23:53, azah azah wrote: > > Thanks Chris and Rod. > > > > I think I have a problem because not many function existing in > > postresql that i installed. > > How can i get all the build-in functions because the basic function > > to_char is not existing in the database? > > Which version of PostgreSQL are you using? > > select version(); > > > > > On Tue, 20 Jul 2004 22:39:38 -0400, Rod Taylor <[EMAIL PROTECTED]> wrote: > > > On Tue, 2004-07-20 at 20:55, azah azah wrote: > > > > Hi, > > > > I want convert from mysql to postresql, > > > > in mysql, query to database using the code as below: > > > > > > > > date_format(submittime, "%W %M %e, %Y - %r") > > > > > > to_char(submittime, 'format string') > > > > > > http://www.postgresql.org/docs/7.4/static/functions-formatting.html > > > > > > > > > ---(end of broadcast)--- > > > TIP 9: the planner will ignore your desire to choose an index scan if your > > > joining column's datatypes do not match > > > > > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] next integer in serial key
hi, how does one get the next number in a serial type row? -- regards kg http://www.onlineindianhotels.net - hotel bookings reservations in over 4600 hotels in India http://www.ootygolfclub.org ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] next integer in serial key
On Thu, 2004-07-22 at 03:45, Kenneth Gonsalves wrote: > hi, > how does one get the next number in a serial type row? When inserting a new row, do not mention the serial column in the list of columns, or else give it the value DEFAULT. -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA "Then Peter and the other apostles answered and said, We ought to obey God rather than men." Acts 5:29 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
