Re: [BUGS] Weird message when creating PK constraint named like table
Hello, r.
You wrote:
rd> When I do this
rd> CREATE TABLE "T1"
rd> (
rd> "T1_ID" bigint NOT NULL,
rd> CONSTRAINT "T1" PRIMARY KEY ("T1_ID" )
rd> );
rd> I get the following message:
rd> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "T1" for
table "T1"
rd> ERROR: relation "T1" already exists
rd> ** Error **
rd> ERROR: relation "T1" already exists
rd> SQL state: 42P07
rd> It does NOT create either the table or the constraint, and the
rd> message is confusing because there is no relation by that name.
rd> The SQLSTATE 42P07 is described in the manual as only as "table
rd> undefined", and it is not clear if the intent is to allow or
rd> disallow the creation of a constraint called the same as the
rd> table in Postgresql. Oracle 11g allows this, but my feeling is that
rd> doing this should not be allowed, just as Postgresql handles it.
rd> I am complaining about the confusing error message which IMO is
rd> off-topic, not about how the DB handles this.
rd> Seen on Postgresql 9.1.2 Linux 64bit.
The quick answer is PRIMARY KEY constraint always has underlying system index
with the same name. Thus to implement CREATE statement above PostgreSQL should
create table with the name “T1″ and the index with the same name. This is
impossible,
because tables and indexes are stored in the same system catalog pg_class (they
share
the same namespace). That is where ambiguity appears. The same is true
for UNIQUE constraint.
On the other hand you may freely create CHECK constraint under such conditions:
CREATE TABLE "T1"
(
"T1_ID" bigint NOT NULL,
CONSTRAINT "T1" CHECK ("T1_ID" > 0 )
);
--
With best wishes,
Pavel mailto:pa...@gf.microolap.com
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Weird message when creating PK constraint named like table
Kevin Grittner, 11.01.2012 21:16:
When I do this
CREATE TABLE "*T1*"
(
"T1_ID" bigint NOT NULL,
CONSTRAINT "*T1*" PRIMARY KEY ("T1_ID" )
);
I get the following message:
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"T1" for table "T1"
ERROR: relation "T1" already exists
SQL state: 42P07
Hmm. If I create them with the asterisks as part of the relation
names, I see the asterisks in the messages:
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"*T1*" for table "*T1*"
ERROR: relation "*T1*" already exists
The * around the table name was added by the translation from the HTML to a
plain text email.
If you display the HTML version of the original posting the name is written in bold, and
I gues the plain text "converter" simply adds the asterisks as that is the
usual convention for bold in plain text emails.
The original SQL is:
CREATE TABLE "T1"
(
"T1_ID" bigint NOT NULL,
CONSTRAINT "T1" PRIMARY KEY ("T1_ID" )
);
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #6395: Invalid XPath expression
The following bug has been logged on the website:
Bug reference: 6395
Logged by: Franco Ricci
Email address: franco.ri...@phys.uniroma1.it
PostgreSQL version: 9.1.2
Operating system: FreeBSD 9
Description:
Query at bottom returns:
"ERROR: invalid XPath expression
DETAIL: Invalid expression
** Error **
ERROR: invalid XPath expression
SQL state: XX000
Detail: Invalid expression"
The same query on PostgreSQL 9.x works fine!
--
SELECT
xpath('/llrp:RO_ACCESS_REPORT/llrp:TagReportData/llrp:*[contains(name(),\"EPC\")]/llrp:EPC/text()',
'http://www.llrp.org/ltk/schema/core/encoding/xml/1.0";
Version="1" MessageID="8">
04954524D0004980B0DF0001
100
'::xml
, ARRAY[ARRAY['llrp',
'http://www.llrp.org/ltk/schema/core/encoding/xml/1.0']])
--
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6395: Invalid XPath expression
* franco ricci:
> The same query on PostgreSQL 9.x works fine!
I think you mean 9.0.
> xpath('/llrp:RO_ACCESS_REPORT/llrp:TagReportData/llrp:*[contains(name(),\"EPC\")]/llrp:EPC/text()',
Does it work if you drop the '\'?
PostgreSQL 9.1 defaults to standard_conforming_strings=on. See the
release notes for details.
--
Florian Weimer
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6393: cluster sometime fail under heavy concurrent write load
Maxim Boguk writes: > PS: no such effects happens on 9.0.4 > I think it can be related to TOAST related changes in 9.0.6 Yes, this is a consequence of commit 44b6d53b467bfe848c34c7a8a174779bb2f43c39, which tried to preserve toast OIDs when doing a table rewrite. The problem is that CLUSTER needs to copy both the old and new versions of an updated row, since the old one is only "recently dead", and both of those versions are pointing at the same TOAST item because the update didn't affect the toasted column. So when we save those versions into the new table separately, we try to write the same TOAST item twice. We could fix this by having toast_save_datum, in the place where it decides to adopt an old toast OID for the value (line 1345 in HEAD), check to see if that OID already exists in the new table. If so, it could either just assume that the value matches, or we could add enough code to verify that there's a match. The latter seems like overkill, and yet I'm not 100% comfortable with just assuming a collision is OK. Comments? regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6395: Invalid XPath expression
On 12-1-2012 4:58 PM, Florian Weimer wrote:
* franco ricci:
The same query on PostgreSQL 9.x works fine!
I think you mean 9.0.
Yes, I mean 9.0.5
xpath('/llrp:RO_ACCESS_REPORT/llrp:TagReportData/llrp:*[contains(name(),\"EPC\")]/llrp:EPC/text()',
Does it work if you drop the '\'?
Thanks a lot.
Without '\' it works!!
PostgreSQL 9.1 defaults to standard_conforming_strings=on. See the
release notes for details.
Regards
Franco Ricci
--
Franco Ricci
LabIT
Sviluppo Servizi Informatici
Dipartimento di Fisica
Università di Roma "La Sapienza"
Piazzale Aldo Moro, 5
00185
Roma
Italy
tel +390649913449
fax +39064463158
<>
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6393: cluster sometime fail under heavy concurrent write load
I wrote: > Yes, this is a consequence of commit > 44b6d53b467bfe848c34c7a8a174779bb2f43c39, which tried to preserve toast > OIDs when doing a table rewrite. The problem is that CLUSTER needs to > copy both the old and new versions of an updated row, since the old one > is only "recently dead", and both of those versions are pointing at the > same TOAST item because the update didn't affect the toasted column. > So when we save those versions into the new table separately, we try to > write the same TOAST item twice. BTW, it strikes me that the preceding implementation had its own bug in this area: in the same situation of having to copy multiple versions of a row, it would create independent copies of the referenced TOAST value. Not only is this space-wasteful, but the copies attached to the already-dead row versions would never be reclaimed, at least not short of another CLUSTER, because they would be inserted into the toast table as live not deleted rows. So preserving toast item OIDs and relying on them for duplicate detection actually will improve matters in a way unrelated to the original purpose of commit 44b6d53b467bfe848c34c7a8a174779bb2f43c39. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Botched estimation in eqjoinsel_semi for cases without reliable ndistinct
On Wed, 11 Jan 2012 19:40:34 -0500, Andres wrote:
> Unfortunately the pastebins where raptelan provided plans expired by
> now... Perhaps he can provide them again?
Sure, the original curiosity I noticed was that adjusting the block
size of results returned by the CTE had widely different effects, 5000
seemed to be some sort of "magic number", while either 6000 or 4000
worked poorly. Originally, our design used blocks of 100,000.
I then noticed a regression with the 5,000 block size. The difference
in the queries between a prototype (that was pretty fast (5-10s) and
what was generated (slow, (5m+)) was that the order of columns in one
of the join conditions in the main query was reversed. ON
source.column = joined.column was slow, while ON joined.column =
source.column was fast. Apparently this is enough to get a different
possible plan to hit the planner first, while another slow plan with
nearly identical estimates is hitting the planner first in other cases.
Attached are three files - one shows the fast plan, another the slow
plan, and another with the query in question. The ON clause where
reversal makes a difference is the td_13 one. I use a different range
in the CTE for both queries as otherwise filesystem cache makes the
timings look better, but in both cases, the CTE returns exactly 5,000
results.
Yes, the query could be written a lot better, but currently it's
generated this way to conform to an expectation of user-defined custom
where clauses that do not qualify column names. Breaking that
compatibility and redoing this better is a longer-term plan.
Please let me know if I'm omitting any important details.
Regards,
--
Casey Allen Shobe | Senior Software Engineer/DBA
Message Systems | http://messagesystems.com
casey.sh...@messagesystems.com | 443-656-3311 x248with "cte_contacts" as (
select distinct on ("c"."contactid")
"c"."contactid" "id"
from "contacts" "c"
join "domains" "d"
using ("domainid")
join "contactdetails" "cd"
using ("contactid")
join "contenttypes" "ct"
using ("contenttypeid")
join "activemailings_230" "a"
using ("contactid")
left join "activemailingsgenerated_230" "g"
using ("progresscounter")
where "a"."progresscounter" between 415000 and 41
and "g"."contactid" is null
order by "c"."contactid"
)
select distinct on ("cd"."contactid", "cd"."progresscounter")
"cd".*,
td_13.td_13,
relation_4.xd_mw_subscriber_domainname
from (
select "am"."progresscounter",
"am"."json",
"am"."segmentid",
"c"."localpart"||'@'||"d"."domainname" as "emailaddress",
"c"."localpart",
"d"."domainname" as "domain",
"c"."statusid",
"d"."domainid",
"d"."domainname",
"ct"."description" as "contenttype",
"cd".*,
"cd"."firstname"||' '||"cd"."lastname" as "fullname"
from "contacts" "c"
join "domains" "d"
using ("domainid")
join "contactdetails" "cd"
using ("contactid")
join "contenttypes" "ct"
using ("contenttypeid")
join "activemailings_230" "am"
using ("contactid")
where "c"."contactid" in (select "id" from "cte_contacts")
) "cd"
left join (
select "contactid",
"tagid" td_13
from "tagdetails"
where "tagid" = '13'
and "contactid" in (select "id" from "cte_contacts")
) as td_13
on td_13."contactid" = "cd"."contactid"
left join (
select "join_contactid",
mw_subscriber.subscriberid as xd_mw_subscriber_subscriberid,
mw_subscriber.countryid as xd_mw_subscriber_countryid,
mw_subscriber.email as xd_mw_subscriber_email,
mw_subscriber.firstname as xd_mw_subscriber_firstname,
mw_subscriber.lastname as xd_mw_subscriber_lastname,
mw_subscriber.address1 as xd_mw_subscriber_address1,
mw_subscriber.address2 as xd_mw_subscriber_address2,
mw_subscriber.city as xd_mw_subscriber_city,
mw_subscriber.stateorprovince as xd_mw_subscriber_stateorprovince,
mw_subscriber.postalcode as xd_mw_subscriber_postalcode,
mw_subscriber.workphone as xd_mw_subscriber_workphone,
mw_subscriber.homephone as xd_mw_subscriber_homephone,
mw_subscriber.dateofbirth as xd_mw_subscriber_dateofbirth,
mw_subscriber.gender as xd_mw_subscriber_gender,
mw_subscriber.issendable as xd_mw_subscriber_issendable,
mw_subscriber.subscribertypeid as xd_mw_subscriber_subscribertypeid,
mw_subscriber.md5_email as xd_mw_subscriber_md5_email,
mw_subscriber.domainname as xd_mw_subscriber_domainname,
mw_subscriber.isdeleted as xd_mw_subscriber_isdeleted,
mw_subscriber.datecreated as xd_mw_subscriber_datecreated,
mw_subscriber.datemodified as xd_mw_subscriber_datemodified
from (
select relation_1."xd_contacts_contactid" as "join_contactid",
subscriber_map.subscriberid as xd_subscriber_map_subscriberid,
subscriber_map.contactid as xd_subscriber_map_contactid
from (
[BUGS] BUG #6396: PgFoundry is down
The following bug has been logged on the website: Bug reference: 6396 Logged by: Bruno Rodrigues Froz Email address: bfro...@gmail.com PostgreSQL version: 9.1.2 Operating system: Windows Description: The web page PgFounfry id out. I want to download the npgsql, but I cannot. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6396: PgFoundry is down
On Fri, Jan 13, 2012 at 01:16, wrote: > The following bug has been logged on the website: > > Bug reference: 6396 > Logged by: Bruno Rodrigues Froz > Email address: bfro...@gmail.com > PostgreSQL version: 9.1.2 > Operating system: Windows > Description: > > The web page PgFounfry id out. I want to download the npgsql, but I cannot. This is not a PostgreSQL bug. (It is known that pgfoundry is down - see the pgsql-www mailinglist where such issues are discussed) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
