[BUGS] BUG #7866: even after T's child-tables are deleted it cannot have select-rule

[tim . romano]

The following bug has been logged on the website:

Bug reference:  7866
Logged by:  Tim Romano
Email address:  tim.rom...@yahoo.com
PostgreSQL version: 9.2.2
Operating system:   Windows 7 x64
Description:

After a table has been inherited, a select-rule cannot be created on it
*even if its child tables are deleted and a vacuum is performed afterwards*.
 

Moreover, the select-rule documentation is vague and incomplete. Instead of
clear and concise definition, with a few simple examples illuminating a
variety of use cases, there is a long, unnecessarily complex, and not very
illuminating example.


create table RLS3
(tenant varchar(25) not null default current_user);
--Query returned successfully with no result in 10 ms.

create table customer
 (id int primary key not null,
 custname varchar(25) not null
 ) INHERITS (RLS3);
 --NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
 -- "customer_pkey" for table "customer"

CREATE RULE "_RETURN" AS ON SELECT TO RLS3 
do instead select * from RLS3 where tenant=current_user;
--ERROR:  could not convert table "rls3" to a view because it has child
tables

drop table customer;
Query returned successfully with no result in 11 ms.
   
 CREATE RULE "_RETURN" AS ON SELECT TO RLS3 
 do instead select * from RLS3 where tenant=current_user;
--ERROR:  could not convert table "rls3" to a view because it has child
tables
 
 VACUUM;
 --Query returned successfully with no result in 91 ms.

CREATE RULE "_RETURN" AS ON SELECT TO RLS3 
do instead select * from RLS3 where tenant=current_user;

--ERROR:  could not convert table "rls3" to a view because it has child
tables



-- 
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 #7866: even after T's child-tables are deleted it cannot have select-rule

[Tom Lane]

tim.rom...@yahoo.com writes:
> After a table has been inherited, a select-rule cannot be created on it
> *even if its child tables are deleted and a vacuum is performed afterwards*.

This isn't a bug, it's an intentional implementation restriction.
I quote from the source code:

 * Are we converting a relation to a view?
 *
 * If so, check that the relation is empty because the storage for the
 * relation is going to be deleted.  Also insist that the rel not have
 * any triggers, indexes, or child tables.  (Note: these tests are too
 * strict, because they will reject relations that once had such but
 * don't anymore.  But we don't really care, because this whole
 * business of converting relations to views is just a kluge to allow
 * loading ancient pg_dump files.)

There is not any expectation that creating a select rule would ever
happen to anything except a freshly created table.  We'd probably not
even support the CREATE RULE syntax for this, except that pg_dump
sometimes needs to do it like that to get around circular dependencies
involving a view's rowtype.

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 #7853: Incorrect statistics in table with many dead rows.

[Jeff Janes]

On Tue, Feb 5, 2013 at 2:00 PM, Kevin Grittner  wrote:
> "jim...@seagate.com"  wrote:
>
>> INFO:  analyzing "public.stream_file"
>> INFO:  "stream_file": scanned 3 of 2123642 pages, containing
>> 184517 live rows and 2115512 dead rows; 3 rows in sample,
>> 158702435 estimated total rows
>
> 184517 live rows in 3 randomly sampled pages out of 2123642
> total pages, means that the statistics predict that a select
> count(*) will find about  13 million live rows to count.
>
>> After "analyze verbose", the table shows 158 million rows. A
>> select count(1) yields 13.8 million rows.
>
> OK, the estimate was 13 million and there were actually 13.8
> million, but it is a random sample used to generate estimates.
> That seems worse than average, but close enough to be useful.
> The 158.7 million total rows includes dead rows, which must be
> visited to determine visibility, but will not be counted because
> they are not visible to the counting transaction.

To clarify here, the 158.7 million estimate does not *intentionally*
include dead rows.  As you say, the ANALYZE did get a very good
instantaneous estimate of the number of live rows.  However, ANALYZE
doesn't over-write the old estimate, it averages its estimate into the
old one.  After the table shape changes dramatically, the ANALYZE
needs to be run repeatedly before the estimate will converge to the
new reality.  (Of course a cluster or vacuum full will blow away the
old statistics, so the next analyze after that will solely determine
the new statistics.)

I agree, not a bug.

Cheers,

Jeff


-- 
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 #7865: Unexpected error code on insert of duplicate to composite primary key

[matti . aarnio]

The following bug has been logged on the website:

Bug reference:  7865
Logged by:  Matti Aarnio
Email address:  matti.aar...@methics.fi
PostgreSQL version: 9.2.2
Operating system:   Fedora Linux 17/18
Description:

A table:

CREATE TABLE example (
   a   TIMESTAMPNOT NULL,
   b   VARCHAR(256) NOT NULL,
   c   VARCHAR(256) NOT NULL,
   PRIMARY KEY(a,b,c) 
);

Inserting a duplicate record on this is returning an SQL Error, but the
status code is 0 instead of expected 23505.

This used to work fine in 8.x series, but is now causing trouble in 9.1.7,
and 9.2.3.

My application filters by the status code to detect if the issue is really
duplicate value, or some database service error.

In a few cases we want to see "it is duplicate!" in order to allow an
operation elsewhere.



-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[BUGS]

[viswam mamilla]

Hi,
This is viswam.

Iam using PostgreSQL 9.1 in my local system.when iam using this database
with vb.net my application gets very slow while retriving data from
database.if i used the same with access or SQL database its is very fast.

Can i know what is the problem.Iam using ODBC Connection for postgresql.

here iam writing one sample table in postgresql.

CREATE TABLE "AppUsers"
(
  "UserID" smallint,
  "Name" character(100),
  "EmailID" character(150),
  "Password" character(20),
  "UserRole" character(50)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "AppUsers"
  OWNER TO postgres;

Re: [BUGS] BUG #7865: Unexpected error code on insert of duplicate to composite primary key

[Tom Lane]

matti.aar...@methics.fi writes:
> CREATE TABLE example (
>a   TIMESTAMPNOT NULL,
>b   VARCHAR(256) NOT NULL,
>c   VARCHAR(256) NOT NULL,
>PRIMARY KEY(a,b,c) 
> );

> Inserting a duplicate record on this is returning an SQL Error, but the
> status code is 0 instead of expected 23505.

Works for me:

regression=# CREATE TABLE example (
regression(#a   TIMESTAMPNOT NULL,
regression(#b   VARCHAR(256) NOT NULL,
regression(#c   VARCHAR(256) NOT NULL,
regression(#PRIMARY KEY(a,b,c) 
regression(# );
CREATE TABLE
regression=# \set VERBOSITY verbose
regression=# insert into example values('today','today','foo');
INSERT 0 1
regression=# insert into example values('today','today','foo');
ERROR:  23505: duplicate key value violates unique constraint "example_pkey"
DETAIL:  Key (a, b, c)=(2013-02-10 00:00:00, today, foo) already exists.
SCHEMA NAME:  public
TABLE NAME:  example
CONSTRAINT NAME:  example_pkey
LOCATION:  _bt_check_unique, nbtinsert.c:398

I'm guessing you have a client-side problem, but since you've said
nothing about what the client-side software is, it's hard to venture
anything more detailed.

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]

[John R Pierce]

On 2/9/2013 3:25 AM, viswam mamilla wrote:
Iam using PostgreSQL 9.1 in my local system.when iam using this 
database with vb.net  my application gets very slow 
while retriving data from database.if i used the same with access or 
SQL database its is very fast.


Can i know what is the problem.Iam using ODBC Connection for postgresql.


you'd likely be better off with npgsql, which is a native .net data 
provider for postgres, instead of ODBC.




here iam writing one sample table in postgresql.

CREATE TABLE "AppUsers"
(
  "UserID" smallint,
  "Name" character(100),
  "EmailID" character(150),
  "Password" character(20),
  "UserRole" character(50)
)


you realize 'character(n)' are fixed length fields?   so if name is 'joe 
blow', it will be padded to 100 characters with spaces?   thats almost 
never what you want to do.  instead use varchar(100) or character 
varying(100) (same thing), or just use text (no length specified), these 
formats return just what you store ('joe blow' will return an 8 char 
string).




--
john r pierce  37N 122W
somewhere on the middle of the left coast