Re: [GENERAL] String Comparison and NULL
On Apr 29, 7:36 am, [EMAIL PROTECTED] (Tom Lane) wrote: > Andreas Kretschmer <[EMAIL PROTECTED]> writes: > >>> ... and I do something like "select id where animal <> 'Cat';" then > >>> shouldn't 1, 3, 4 and 5 be picked? As it is I only get 1, 4 and 5. > >>> NULL is not 'Cat'. I realize that if I were testing for NULL itself I > > NULL is nothing, you can't compare something with nothing. > > A better way to think about it is that NULL means UNKNOWN. Thus > the result of NULL <> 'Cat' is not FALSE but UNKNOWN (ie NULL) > --- if you don't know what the value is, you don't know whether or not > it's equal to any specific other value. > > The other mistake novices typically make is to expect that > NULL = NULL will yield TRUE. It doesn't, it yields NULL, > because again you can't say whether two unknown quantities > are equal. > > You can hack around this behavior to some extent with > IS DISTINCT FROM, but generally the right thing is to redesign > your data representation. Trying to make NULL act like a normal > data value is almost always going to lead to tears in the long run. > > regards, tom lane > > -- > Sent via pgsql-general mailing list ([EMAIL PROTECTED]) > To make changes to your > subscription:http://www.postgresql.org/mailpref/pgsql-general This is a lot of good information and really helps. I think I'll rework my DB design and client program to try and avoid this behavior all together. I'm sure that'll be the best option in the long run. Thanks to everyone for the help! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] complex query using postgresql
Hi everybody
I have de following table where i can perform two different queries:
select * from precalc where idr(p1, p4, p6, p7, p9, 'HLA-DR7')>2; where idr
is a function used to create indicies
and
select * from precalc where p1='S';
Now i would like to perform a query as :
select * from precalc where idr(p1, p4, p6, p7, p9, 'HLA-DR7')>2 and
p1='S'...;
but i don't know how
any body can help me?
thanks
Column | Type | Modifiers
+--+--
id | integer | not null default
nextval('precalc_id_seq'::regclass)
p1 | character(1) |
p4 | character(1) |
p6 | character(1) |
p7 | character(1) |
p9 | character(1) |
Indexes:
"h2iab" btree (idr(p1, p4, p6, p7, p9, 'H-2*IAb'::character varying))
"h2iad" btree (idr(p1, p4, p6, p7, p9, 'H-2*IAd'::character varying))
"h2iak" btree (idr(p1, p4, p6, p7, p9, 'H-2*IAk'::character varying))
"h2ied" btree (idr(p1, p4, p6, p7, p9, 'H-2*IEd'::character varying))
"hladqa10501" btree (idr(p1, p4, p6, p7, p9, 'HLA-DQA1*0501'::character
varying))
"hladqb10201" btree (idr(p1, p4, p6, p7, p9, 'HLA-DQB1*0201'::character
varying))
"hladr" btree (idr(p1, p4, p6, p7, p9, 'HLA-DR'::character varying))
"hladr1" btree (idr(p1, p4, p6, p7, p9, 'HLA-DR1'::character varying))
"hladr13" btree (idr(p1, p4, p6, p7, p9, 'HLA-DR13'::character varying))
"hladr3" btree (idr(p1, p4, p6, p7, p9, 'HLA-DR3'::character varying))
"hladr7" btree (idr(p1, p4, p6, p7, p9, 'HLA-DR7'::character varying))
"hladrb10101" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101'::character
varying))
"hladrb10102" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0102'::character
varying))
"hladrb10301" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0301'::character
varying))
"hladrb10302" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0302'::character
varying))
"hladrb10401" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0401'::character
varying))
"hladrb10402" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0402'::character
varying))
"hladrb10701" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0701'::character
varying))
"hladrb10802" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0802'::character
varying))
"hladrb10901" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0901'::character
varying))
"hladrb11101" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1101'::character
varying))
"hladrb11102" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1102'::character
varying))
"hladrb11103" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1103'::character
varying))
"hladrb11104" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1104'::character
varying))
"hladrb11301" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1301'::character
varying))
"hladrb11302" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1302'::character
varying))
"hladrb11501" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1501'::character
varying))
"hladrb40101" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB4*0101'::character
varying))
"hladrb50101" btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB5*0101'::character
varying))
"iid" btree (id)
"ip1" btree (p1)
"ip4" btree (p4)
"ip6" btree (p6)
"ip7" btree (p7)
"ip9" btree (p9)
--
Pau Marc Muñoz Torres
Laboratori de Biologia Computacional
Institut de Biotecnologia i Biomedicina Vicent Villar
Universitat Autonoma de Barcelona
E-08193 Bellaterra (Barcelona)
telèfon: 93 5812807
Email : [EMAIL PROTECTED]
Re: [GENERAL] complex query using postgresql
On Apr 30, 2008, at 11:50 AM, Pau Marc Munoz Torres wrote:
Hi everybody
I have de following table where i can perform two different queries:
select * from precalc where idr(p1, p4, p6, p7, p9, 'HLA-DR7')>2;
where idr is a function used to create indicies
Are your indices generated by the function in your where clause? It
is apparently computing something as well (probably its main
purpose?). I'm not sure I understand what you mean here, as I
understand it it seems a very odd thing to do...
and
select * from precalc where p1='S';
Now i would like to perform a query as :
select * from precalc where idr(p1, p4, p6, p7, p9, 'HLA-DR7')>2
and p1='S'...;
but i don't know how
any body can help me?
What is the problem with just performing that query? What is it you
need help with?
Looking at the enormous number of indices you have on that table, I
can't help to wonder whether your design is right. Updating that
table is going to be a pain with that many indices and that many
calculations going on, and choosing the right index to use for a
query is might be difficult for the query planner, as it's cost-based.
I get the impression that the precalc table stores the input
parameters for your function and you store the pre-computed values in
different indices, with the only other variable being that code in
the last parameter to your function?
Wouldn't your problem be solved mostly by adding a column for those
codes and create a single index over idr(code, p1, p4, p6, p7, p9) ?
That would replace most of the indices you have now by one index. If
you make that code a foreign key to a table containing only the
possible codes you'd have their values constrained too. I think
effectively your p1-p9 and that code columns are the primary key of
your table, not sure what your plans with the id column are (which
you didn't make a PK btw as I think you intended to).
The query in your original question would probably be turned into a
bitmap index scan between this new index and your "ip1" index by the
query planner. That's likely to perform fine, as the number of
possible values for p1-9 and code are rather finite; at most (127-32)
^5 * 29 = 224 billion integers, if I counted correctly and assuming
you're using ASCII characters. Probably significantly less if you
don't need results for all possible values for p1-p9.
If this is too much, you could partition that table on code,
effectively turning it into 29 tables constrained on a specific code
value, each with their own index over (p1, p4, p6, p7, p9, code).
With an approach like this I wonder whether it'll be worthwhile, it
may actually be faster to compute your function result on the fly
instead of pre-calculating it when it's input parameters are
inserted. I do hope the function is defined immutable (it has to
behave like that, considering your index usage)?
thanks
Column | Type | Modifiers
+--
+--
id | integer | not null default nextval
('precalc_id_seq'::regclass)
p1 | character(1) |
p4 | character(1) |
p6 | character(1) |
p7 | character(1) |
p9 | character(1) |
Indexes:
"h2iab" btree (idr(p1, p4, p6, p7, p9, 'H-2*IAb'::character
varying))
"h2iad" btree (idr(p1, p4, p6, p7, p9, 'H-2*IAd'::character
varying))
"h2iak" btree (idr(p1, p4, p6, p7, p9, 'H-2*IAk'::character
varying))
"h2ied" btree (idr(p1, p4, p6, p7, p9, 'H-2*IEd'::character
varying))
"hladqa10501" btree (idr(p1, p4, p6, p7, p9, 'HLA-
DQA1*0501'::character varying))
"hladqb10201" btree (idr(p1, p4, p6, p7, p9, 'HLA-
DQB1*0201'::character varying))
"hladr" btree (idr(p1, p4, p6, p7, p9, 'HLA-DR'::character
varying))
"hladr1" btree (idr(p1, p4, p6, p7, p9, 'HLA-DR1'::character
varying))
"hladr13" btree (idr(p1, p4, p6, p7, p9, 'HLA-DR13'::character
varying))
"hladr3" btree (idr(p1, p4, p6, p7, p9, 'HLA-DR3'::character
varying))
"hladr7" btree (idr(p1, p4, p6, p7, p9, 'HLA-DR7'::character
varying))
"hladrb10101" btree (idr(p1, p4, p6, p7, p9, 'HLA-
DRB1*0101'::character varying))
"hladrb10102" btree (idr(p1, p4, p6, p7, p9, 'HLA-
DRB1*0102'::character varying))
"hladrb10301" btree (idr(p1, p4, p6, p7, p9, 'HLA-
DRB1*0301'::character varying))
"hladrb10302" btree (idr(p1, p4, p6, p7, p9, 'HLA-
DRB1*0302'::character varying))
"hladrb10401" btree (idr(p1, p4, p6, p7, p9, 'HLA-
DRB1*0401'::character varying))
"hladrb10402" btree (idr(p1, p4, p6, p7, p9, 'HLA-
DRB1*0402'::character varying))
"hladrb10701" btree (idr(p1, p4, p6, p7, p9, 'HLA-
DRB1*0701'::character varying))
"hladrb10802" btree (idr(p1, p4, p6, p7, p9, 'HLA-
DRB1*0802'::character varying))
"hladrb10901" btree (idr(p1, p4, p6, p7, p9, 'HLA-
DRB1*0901'::character varying))
"hladrb11101" btree (idr(p1, p4, p6, p7,
Re: [GENERAL] Multibyte (Japanese Character) Sorting
I have taken a look at the screen shot. Yes, the sort order seems pretty ridiculous. I tested similar data on my Linux box and the result was nothing strange. Do you have an index on the field? What is the platform PostgreSQL is running on? Do you see the same problem using psql? Can you give me the pg_dump data if possible? -- Tatsuo Ishii SRA OSS, Inc. Japan > Thank you for your reply. But I believe our LOCALE was already set to C > (since this is the default setting). > > I've attached the result of my query using "ORDER BY ASC". This > field contains double byte character for both english and japanese text. > I think the problem with this sorting is, it sorts by length then by > ascii code value. > > Tatsuo Ishii wrote: > >> Hi there, > >> > >> Im having a problem in sorting multibyte characters. > >> > >> I am using EUC-JP for my database encoding becuase we need to support > >> japanese (hiragana, katakana, kanji) text, since our clients are japanese. > >> > >> I have a table named "user_info" with the following fields: > >> > >> first_name character(60) NOT NULL > >> last_name character(60) NOT NULL > >> > >> We've forced doublebyte character our entries so that all data stored in > >> the table are doublebyte. The problem is, the sorting procedure. when > >> you user ORDER BY last_name ASC, the list is not sorted properly. Please > >> help me fix this problem. Thank you in advanced. > > > > I'm not sure why you think "not sorted properly", but my wild guess is > > your OS's locale data is broken. Use C locale. > > -- > > Tatsuo Ishii > > SRA OSS, Inc. Japan > > > > > > -- > == > Morgan Gonzales - 1st BU (MSI) - Tsukiden Software > > There are two kinds of people in this world. > One says to God, thy will be done, > and the other to whom God says, thy will be done. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Quoting "
In a test I just did, the sequence \" (backslash double-quote) is interpreted as just a " inside of the E'...' string constant expression. This is great, since PHP's addslashes() sticks them in along with the other stuff I really need to quote like ' and \. But I see that \" isn't documented in the manual in section 4.1.2.1. I assume this is, in fact, standard behavior for Postgres? (Perhaps a comprehensive table might be a good idea at this point in the manual.) Thanks, -- Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] inheritance. more.
On Tue, Apr 29, 2008 at 11:47 AM, Martijn van Oosterhout <[EMAIL PROTECTED]> wrote: > On Tue, Apr 29, 2008 at 06:31:30AM +0530, Gurjeet Singh wrote: > > On Mon, Apr 28, 2008 at 8:01 PM, Erik Jones <[EMAIL PROTECTED]> wrote: > > > Postgres doesn't yet handle inheritance of constraints from parent to > > > child tables via inheritance. > > > Was it done by design or was it a limitation we couldn't get over? > > Inheritence of most constraints works, just not unique constraints. The > problem of managing a unique index over multiple tables has not yet > been solved (it's a reasonably hard problem). > > I completely agree with the difficulty of the problem. One of the advantages of breaking up your data into partitions, as professed by Simon (I think) (and I agree), is that you have smaller indexes, which improve performance. And maybe having one huge index managing the uniqueness across partitioned data just defeats the idea of data partitioning! Best regards, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com Mail sent from my BlackLaptop device
[GENERAL] Performing a sub-query in a SELECT SUM aggregate.
Hello, I'm writing a large pl/pgSQL function to batch process raw sales records into departmentalised totals, stored in a dedicated table. The logical thing to do is to INSERT a conditional aggregate ( INSERT INTO . SELECT SUM CASE WHEN ...ELSE... FROM. GROUP BY ) INTO that dedicated table. However, I need to do things like INSERT special records into some other table sometimes. For example, if the sales were made on credit, I need to make a dedicated record of that in the credit accounts table. I'd like to do this within the conditional aggregate, when it encounters a record that requires it, because that's more logical, direct and efficient, rather then do the aggregate and then do separate,"special" things on the same data. Is this possible? If not, could someone write some psuedo code that would accomplish the same think efficiently using cursors or something? Thanks, Peter Geoghegan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Monday 28 April 2008 10:28, Andrew Sullivan wrote:
> On Sat, Apr 26, 2008 at 08:33:28PM -0400, Robert Treat wrote:
> > enum types custom ordering. It also showcases the idea of data
> > definitions that "should never change", but that do changes every half
> > dozen years or so. Now you can argue that since it is expected that the
> > ratings might change in some way every few years that an enum type is not
> > a good choice for this, but I feel like some type of counter-argument is
> > that this is probably longer than one would expect thier database
> > software to last. :-)
>
> I think that if you are building software on the premise that it's
> only going to last five years, you oughta have a look around on the
> Internet again. Or think about why banks spent the money they did a
> few years back poring over ancient code making sure that two-digit
> year representations weren't in use.
>
If one were to have built something on postgresql 5 years ago, they would have
had to do it on 7.3. Whenever anyone posts a problem on 7.3, the first thing
people do now days is jump up and down waving thier arms about while
exclaiming how quickly they should upgrade. While I am certain there are even
older versions of postgres still running in production out there, I'd have to
say that the core developers for this project do not release software with
the expectation that you will use if for more than 5 years.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Quoting "
Andy Anderson <[EMAIL PROTECTED]> writes: > In a test I just did, the sequence \" (backslash double-quote) is > interpreted as just a " inside of the E'...' string constant > expression. This is great, since PHP's addslashes() sticks them in > along with the other stuff I really need to quote like ' and \. But I > see that \" isn't documented in the manual in section 4.1.2.1. Huh? It says Any other character following a backslash is taken literally. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] inheritance. more.
> And maybe having one huge index managing the uniqueness across partitioned > data just defeats the idea of data partitioning! Except when you want uniqueness across all partitions. Karsten -- Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen! Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Quoting "
Andy Anderson wrote: In a test I just did, the sequence \" (backslash double-quote) is interpreted as just a " inside of the E'...' string constant expression. This is great, since PHP's addslashes() sticks them in along with the other stuff I really need to quote like ' and \. But I see that \" isn't documented in the manual in section 4.1.2.1. I assume this is, in fact, standard behavior for Postgres? (Perhaps a comprehensive table might be a good idea at this point in the manual.) craig=# SELECT E'\z\v\k\-'; ?column? -- zvk- (1 row) From 4.1.2.1: ... "Any other character following a backslash is taken literally." I didn't see any escape sequences being interpreted other than those that're already documented in the section of 4.1.2.1 that describes E'' strings. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Quoting "
Ah, slight ambiguity here. Perhaps this might best say "Any other character following a backslash is taken literally, and the backslash is removed." Thanks, -- Andy On Apr 30, 2008, at 10:28 AM, Tom Lane wrote: Andy Anderson <[EMAIL PROTECTED]> writes: In a test I just did, the sequence \" (backslash double-quote) is interpreted as just a " inside of the E'...' string constant expression. This is great, since PHP's addslashes() sticks them in along with the other stuff I really need to quote like ' and \. But I see that \" isn't documented in the manual in section 4.1.2.1. Huh? It says Any other character following a backslash is taken literally. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deadlock situation?
> Well, there went the evidence :-( ... but what exactly did you have > to do to shut it down? I'm wondering whether the backends responded > to SIGINT or SIGTERM. Sorry :( First, I tried issuing a kill to the pid of the vacuum process. It didn't respond to kill. Then, I tried a kill -9. It responded to that :) However, postgres got very unhappy at this point - I tried to log in to issue another query against the pg_stat_activity table, but every command I issued at this point simply hung. In this case, Postgres had been started in the foreground on a terminal, so I went to that terminal, and did a ctrl-c. Eventually, postgres stopped, but the terminal wouldn't respond either - and I had to close it. > > Next time, it'd be good to confirm (with top or vmstat or similar) > whether the backends are actually idle or are eating CPU or I/O. I didn't notice any high load on the processor - the server seemed idle. But I didn't look real close a the individual postgres processes. > Also try strace'ing a few of them; the pattern of kernel calls if > any would be revealing. > > The lack of deadlock reports or 't' values in pg_stat_activity.waiting > says that you weren't blocking on heavyweight locks. It's not > impossible that there was a deadlock at the LWLock level, though. > > What sort of indexes are there on this table? Here is the DDL for the table where vacuum was trying to run: CREATE TABLE iphost ( ethernetmacaddr char(17) NOT NULL, cpemac char(11) NOT NULL, ipaddr varchar(15) NOT NULL, regtime timestamp NOT NULL, leasetime timestamp, last_updated timestamp NOT NULL DEFAULT now(), CONSTRAINT pk_iphost PRIMARY KEY (ethernetmacaddr, ipaddr), CONSTRAINT fk_iphost_cpe FOREIGN KEY (cpemac) REFERENCES cpe (cpemac) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE ) WITHOUT OIDS; ALTER TABLE iphost OWNER TO pslogin; CREATE INDEX ix_iphost_cpemac ON iphost USING btree (cpemac); CREATE INDEX ix_iphost_ipaddr ON iphost USING btree (ipaddr); > Teodor just fixed > an issue in GIN indexes that involved taking an unreasonable number of > LWLocks, and if that code wasn't exposing itself to deadlock risks > I'd be pretty surprised. > > regards, tom lane Thanks. If/when I can recreate this, I'll try to gather more info. Dan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
Robert Treat wrote: If one were to have built something on postgresql 5 years ago, they would have had to do it on 7.3. Whenever anyone posts a problem on 7.3, the first thing people do now days is jump up and down waving thier arms about while exclaiming how quickly they should upgrade. [snip] > I'd have to > say that the core developers for this project do not release software > with the expectation that you will use if for more than 5 years. From what I've seen on the list so far upgrades are advised precisely because somebody has had to ask for help with a problem - one that's usually resolved in newer versions. Doubly so because the issues raised are usually performance related or are caused by limitations in 7.3 (or whatever they're running). If upgrading solves a problem that somebody is asking for help with, then advising the user to upgrade makes sense. Consider operating systems. An OS vendor might expect a given version to be used for five years or more, but if you ask them for help because you're encountering awful performance with 2TB disks or high CPU load on 10GigE networks they're quite likely to advise you to upgrade the OS to a version with enhancements that solve your problems. That says nothing about the people out there still using 7.3 and similar without problems, running well within its capabilities and happy with what it's doing. I doubt many people would advise them to upgrade - at least not in a hurry and not with any jumping and hand-waving. I often see responses along the lines of "if you're using 8.3 then just do otherwise you'll need to" - so there doesn't appear to be any assumption that the first step must be to upgrade to the latest version. My impression from using PostgreSQL is that people using old versions are taken seriously. Data corruption, crash and security bug fixes get applied to very old versions. For example, 7.3.21 was released on Jan 2008, and includes several fixes: http://www.postgresql.org/docs/current/static/release-7-3-21.html Given that 7.3 was released in late 2002: http://www.postgresql.org/docs/current/static/release-7-3.html I think that's pretty good myself. Given that PostgreSQL upgrades aren't trivial, it's important to have these ongoing releases for older versions. It's great to see that need so well met. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] inheritance. more.
"Karsten Hilbert" <[EMAIL PROTECTED]> writes: >> And maybe having one huge index managing the uniqueness across partitioned >> data just defeats the idea of data partitioning! > Except when you want uniqueness across all partitions. Well, the point was that if the partitioning arrangement guarantees to put distinct ranges of the key into distinct tables, then a separate unique constraint on each table would suffice to guarantee global uniqueness. You can set up such a thing today, but it's a manual jury-rigged affair. An automatic partitioning system would be a lot nicer. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Wed, Apr 30, 2008 at 10:19:25AM -0400, Robert Treat wrote: > exclaiming how quickly they should upgrade. While I am certain there are even > older versions of postgres still running in production out there, I'd have to > say that the core developers for this project do not release software with > the expectation that you will use if for more than 5 years. You're equivocating here. The PostgreSQL project is under active development, and there are supported and unsupported versions. Moreover, it's not really just an application; it's more like application infrastructure. The database engine one uses for an application is more like a shared library, from the application's point of view, than it is like another application. Most application software doesn't remain under active development once delivered. You write it to the point where it works, and then when it is doing the job expected, it starts to be used. There is no maintenance, and there are no opportunities for new nifty features. This is _especially_ true of bespoke software, which is why most of it is so awful when you look at it: there's no incentive at all to make the code maintainable, because it will never get any maintenance anyway. Think of the difference between making durable goods like washing machines or automobiles, as compared to more or less disposable goods like kitchen gadgets or, these days, toasters and blenders. The former either come with or have available for them spare parts, schematic documents, and long warranties. The latter come with, if you're lucky, a warranty of a year and warnings that there are no servicable parts inside. Yet people expect to use their toasters and kitchen gadgets for years, and are annoyed if they fail after just a year. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deadlock situation?
"Dan Armbrust" <[EMAIL PROTECTED]> writes: >> What sort of indexes are there on this table? > [ just btrees ] Okay, that lets the GIN stuff off the hook. I wonder whether you saw some variant of the deadlock reported in bug #3883 --- see discussion here: http://archives.postgresql.org/pgsql-hackers/2008-01/msg00873.php The only known way of getting into that problem is fixed in 8.3, but a potential for silent deadlock is definitely still there. I don't however have any idea how such a deadlock could occur without at least some processes showing as 'waiting' in pg_stat_activity. Do you have any idea what the transactions that were blocked at COMMIT had been doing? Are there any foreign key constraints leading *to* this table? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Quoting "
On Wed, Apr 30, 2008 at 7:10 AM, Andy Anderson <[EMAIL PROTECTED]> wrote: > In a test I just did, the sequence \" (backslash double-quote) is > interpreted as just a " inside of the E'...' string constant expression. > This is great, since PHP's addslashes() sticks them in along with the other > stuff I really need to quote like ' and \. But I see that \" isn't > documented in the manual in section 4.1.2.1. I assume this is, in fact, > standard behavior for Postgres? (Perhaps a comprehensive table might be a > good idea at this point in the manual.) Why are you using php's addslashes() function? Is there something missing from pg_escape_string()??? Or are you doing something else I'm not thinking of? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why is postgres autovacuuming a table that is never updated?
Andrew Sullivan wrote: 2) How can I use pg_autovacuum table to disable autovac for this table? The docs are not clear on how to do this. I think that would be a bad idea, given that autovacuum seems to think you need to do it. I don't want to totally disable it, but I want to change the numbers so it happens less frequently. Since there are no updates I can set freeze_min_page to be small and the max between transactions big to keep the autovacuum from happening so often. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why is postgres autovacuuming a table that is never updated?
Joseph S wrote: > Andrew Sullivan wrote: > >> >>> 2) How can I use pg_autovacuum table to disable autovac for this >>> table? The docs are not clear on how to do this. >> >> I think that would be a bad idea, given that autovacuum seems to think >> you need to do it. > > I don't want to totally disable it, but I want to change the numbers so > it happens less frequently. Since there are no updates I can set > freeze_min_page to be small and the max between transactions big to keep > the autovacuum from happening so often. Agreed, that's a good strategy. You only need to keep an eye on how often is pg_clog going to be trimmed. (The only disadvantage is how much spaces it occupies on disk.) -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deadlock situation?
> I wonder whether you saw some variant of the deadlock reported in bug > #3883 --- see discussion here: > http://archives.postgresql.org/pgsql-hackers/2008-01/msg00873.php > The only known way of getting into that problem is fixed in 8.3, > but a potential for silent deadlock is definitely still there. > I don't however have any idea how such a deadlock could occur without > at least some processes showing as 'waiting' in pg_stat_activity. > > Do you have any idea what the transactions that were blocked at > COMMIT had been doing? Should have just been selects and updates, and perhaps some inserts and deletes - nothing to out of the ordinary or complex. If I can get this to happen consistently, I'll set up a logger to capture all of the SQL statements leading up to the freeze. > > Are there any foreign key constraints leading *to* this table? Nope. Unfortunately, I had been running for a couple of weeks steady before I got the error - so reproducing it may not be quick. But I'm going to set up a couple of parallel tests on more systems, and see if I can happen into it again sooner. Oh, and I don't think though should have anything to do with it, but I did discover that fsync was turned off on this postgres instance. Someone else didn't clean up after a different test, and I forgot to change it back. Thanks, Dan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Quoting "
On Wed, Apr 30, 2008 at 9:10 AM, Andy Anderson <[EMAIL PROTECTED]> wrote: In a test I just did, the sequence \" (backslash double-quote) is interpreted as just a " inside of the E'...' string constant expression. This is great, since PHP's addslashes() sticks them in along with the other stuff I really need to quote like ' and \. On Apr 30, 2008, at 11:52 AM, Scott Marlowe wrote: Why are you using php's addslashes() function? Is there something missing from pg_escape_string()??? Or are you doing something else I'm not thinking of? Yep, relying on what I already know to move forward on my project, rather than browsing the manual for things I didn't know I needed :-). Thanks for the tip. -- Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deadlock situation?
> In this case, Postgres had been started in the foreground on a > terminal, so I went to that terminal, and did a ctrl-c. Eventually, > postgres stopped, but the terminal wouldn't respond either - and I had > to close it. Just out of curiosity, could you maybe have XOFF'd the terminal? I've had cases in the past where Postgres got unhappy and froze when it couldn't write logging information to stderr. (Though, granted, in the cases I hit the server's stderr was redirected to a pipe that was full, so it's not 100% analogous) -Dan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] question/problem concerning GRANT/REVOKE
On Tue, Apr 29, 2008 at 2:46 AM, Gerhard Wohlgenannt <[EMAIL PROTECTED]> wrote: > hi! > > I have a pretty basic problem: We have several schemas in one of our > databases, and we need the users to see only the tables (and table > structure) of tables inside their own schema. So I created schemas for > those users, and set their "search_path". > > But with \d public. users can see all tables (and their structure) > in the public (and other) schemas -- and I found no way yet to prevent > this?? > I have tried "REVOKE ALL" from the database itself, and the other schemas, > and single tables -- for the specific user, and also for the user "PUBLIC" > -- but it had no effect! > How can I prevent exposing the structure of all tables in the database to > user that should work in their SCHEMA? > > Help would be appreciated very much!! :-) This is a semi frequently asked question. There's no mechanism built into pgsql that allows you to hide other schemas and objects this way. Note that if you have the proper security setup, all they can do is see that the other tables are there. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Closed ResultSet error
I am migrating from another database to PostgreSQL and have run into a serious problem. The Java program runs fine on the other DB but under PostgreSQL it gives the error, "ResultSet is closed." This result set is created in a method that processes each row sequentially. Within the processing, it passes the result set to another method that retrieves columns from it and creates additional result sets to retrieve related information. This second method can also call itself recursively. This works fine with HSQLDB but fails when returning back to the original method and attempting another loop on the original result set, claiming it is closed. I have removed all occurrences of closing any result sets and there are no instances of closing underlying statements within those methods. Finally, I have stepped through the entire operation with a debugger and found no unexpected events. Each time a new result set was created, the debugger indicated it to be a new entity. When finally returning to the original, it appeared to be the correct instance. Is there a known problem along these lines? Any help or suggestions will be most appreciated. Thanks, Bayless
Re: [GENERAL] Closed ResultSet error
On Wed, 30 Apr 2008, Bayless Kirtley wrote: I am migrating from another database to PostgreSQL and have run into a serious problem. The Java program runs fine on the other DB but under PostgreSQL it gives the error, "ResultSet is closed." I have removed all occurrences of closing any result sets and there are no instances of closing underlying statements within those methods. Finally, I have stepped through the entire operation with a debugger and found no unexpected events. Each time a new result set was created, the debugger indicated it to be a new entity. When finally returning to the original, it appeared to be the correct instance. When the same statement is used to execute a second query, the first ResultSet is closed automatically. I'm guessing that this is what's happening to you and you need to be sure to create a new Statement for each concurrently open ResultSet you have. Kris Jurka -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Full Text Search. In 8.3.1 How ?
Hello List. I need to make finding precise words or phrases in Word documents (I put this document as plain text to a field text type of a table in my database). I'm working with PostgreSQL 8.3.1 on Linux; before, working with tsearch2-in version 8.2.x-could find presence or absence of a word in the text field, in reality what I need is to count the number of occurrences in this field a word or phrase perfect (two or more words that appear in the same order). I repeat: what we need is "counting" how many times you see a pattern in that field type text. Be one of you who has done something similar to me of some kind to improve what I am doing?. Thanks in advance. Rubén
[GENERAL] Trouble with text search select statement
I am having trouble getting a multiple column text search select statement
to work.
I've setup the following:
CREATE TABLE tstest (col1 text, col2 text);
INSERT INTO tstest (col1, col2) VALUES ('bird', 'moon');
CREATE INDEX tstest_ts_idx ON tstest USING gin(to_tsvector('english',
coalesce(col1, '') || '' || coalesce(col2, '')));
I then execute what I believe is the correct select statement:
SELECT * FROM tstest WHERE to_tsvector('english', coalesce(col1, '') || ''
|| coalesce(col2, '')) @@ to_tsquery('english', 'bird');
If I remove the second column from the to_tsvector in the select statement
then it returns the row.
SELECT * FROM tstest WHERE to_tsvector('english', coalesce(col1, '')) @@
to_tsquery('english', 'bird');
I know I've missed something basic but I can't figure out what.
As a side note I don't understand the purpose of the || '' || between the
columns in the to_tsvector. I saw it in several examples. I've tried both
with and without it with no change.
Thanks in advance.
-Mont
Re: [GENERAL] Trouble with text search select statement
Mont Rothstein wrote:
I am having trouble getting a multiple column text search select
statement to work.
I've setup the following:
CREATE TABLE tstest (col1 text, col2 text);
INSERT INTO tstest (col1, col2) VALUES ('bird', 'moon');
CREATE INDEX tstest_ts_idx ON tstest USING gin(to_tsvector('english',
coalesce(col1, '') || '' || coalesce(col2, '')));
I then execute what I believe is the correct select statement:
SELECT * FROM tstest WHERE to_tsvector('english', coalesce(col1, '')
|| '' || coalesce(col2, '')) @@ to_tsquery('english', 'bird');
If I remove the second column from the to_tsvector in the select
statement then it returns the row.
SELECT * FROM tstest WHERE to_tsvector('english', coalesce(col1, ''))
@@ to_tsquery('english', 'bird');
I know I've missed something basic but I can't figure out what.
As a side note I don't understand the purpose of the || '' || between
the columns in the to_tsvector. I saw it in several examples. I've
tried both with and without it with no change.
'' isn't supposed to to be the empty string, it's supposed to be a
quoted space. to_tsvector separates on whitespace. So with the space,
it will have the words bird and moon.Without the space, it will have the
single word birdmoon.
klint.
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: [EMAIL PROTECTED]
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trouble with text search select statement
Thank you. I knew it had to be something simple. Much appreciated. -Mont
Re: [GENERAL] How to modify ENUM datatypes?
On Wed, 30 Apr 2008, Robert Treat wrote: Whenever anyone posts a problem on 7.3, the first thing people do now days is jump up and down waving thier arms about while exclaiming how quickly they should upgrade. While I am certain there are even older versions of postgres still running in production out there, I'd have to say that the core developers for this project do not release software with the expectation that you will use if for more than 5 years. You could easily make a case that 7.3 wasn't quite mature enough overall to be useful for 5 years. There's little reason to keep pumping support effort into something with unfixable flaws. I know when I was using 7.4 heavily, I never felt like that was something I could keep going for that long; the VACUUM issues in particular really stuck out as something I wouldn't be likely to handle on future hardware having larger databases. 8.1, on the other hand, is the first release I thought you could base a long-term effort on, and 8.2 and 8.3 have moved further in that direction. 8.1 has been out for 2.5 years now, and it seems like it's got plenty of useful left in it still (except on Windows). The improvements in 8.2 and 8.3 are significant but not hugely important unless you're suffering performance issues. Compare with 7.3, which came out at the end of 2002. By 2.5 years after that, the project was well into 8.0, which was clearly a huge leap. PITR, tablespaces, whole new buffer strategy, these are really fundamental and compelling rather than the more incremental improvements coming out nowadays. (Obligatory Oracle comparison: for customers with standard support levels, Oracle 8.1 was EOL'd after slightly more than 4 years. It wasn't until V9 that they pushed that to 5 years) -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
