[SQL] Cascading a pseudo-delete?

[Brian Powell]

Greetings, for our database, we keep everything--deleting nothing; however,
to the client side, it should appear deleted.  We do this via a status_cd
for each record.  If the status_cd is for 'deleted' then that item is not
shown to the client-side.

Anyhow, my question is that we also use foreign keys, and I would like to
delete the one record and have the deletion cascade to all records using
that foreign key.  This is no problem, until I try to no longer delete, but,
change the flag.  So, my question is, how can a create a rule which
activates on delete and simply marks the record deleted then cascade that
deletion to all of the objects who are constrained to its key?

Thanks,

--Brian

[SQL] Permissions Broken in 7.0.x?

[Brian Powell]

Greetings, I am having problem with table permissions and view permissions.

Say I am user bob and I own a database, bobdb.  Let us also assume a user,
tester has been added to the db, via create user...

% psql bobdb
psql => create table test (
name   char(32) not null,
ageint4
  );
psql => revoke all on test from tester;
CHANGE
psql => select * from test;
ERROR:  test: Permission denied.

Why would this be so?  I only revoked the user tester. So, I add myself:

Additionally, if I have a trigger on a table which sets the updated_date and
updated_by on any update, and I have a view with a rule which passes any
update to the table (thus calling the trigger), I get a permission denied.
I am supposing that the user updating the view does not have privileges to
the trigger?  How would I grant them privileges to the trigger?

Thank you for your assistance.

--Brian

Re: [SQL] Permissions Broken in 7.0.x?

[Brian Powell]

Tom,

Thank you for the reply.  I have found another issue that I am curious
about:  I have a table with foreign key constraint to itself (a parent_id
field).  I have a view to the table with permissions for a user to insert
into the view (of course, a rule exists to insert into the real table).  The
problem is that it fails because of the constraint!  Any way around this?
Or, am I sort of stuck granting all of these privileges to the table so that
triggers, constraints, etc. will work?

Thanks,
Brian 

> Brian Powell <[EMAIL PROTECTED]> writes:
>> % psql bobdb
>> psql => create table test (
>> name   char(32) not null,
>> ageint4
>>   );
>> psql => revoke all on test from tester;
>> CHANGE
>> psql => select * from test;
>> ERROR:  test: Permission denied.
> 
> Yeah, that's a bug --- confusion about the default permissions set.
> After doing an explicit GRANT or REVOKE on a table you own, you need to
> grant permissions to yourself again; the default assumption that you
> have all permissions gets lost :-(.  This is fixed for 7.1.
> 
>> Additionally, if I have a trigger on a table which sets the updated_date and
>> updated_by on any update, and I have a view with a rule which passes any
>> update to the table (thus calling the trigger), I get a permission denied.
>> I am supposing that the user updating the view does not have privileges to
>> the trigger?  How would I grant them privileges to the trigger?
> 
> Don't think there's a way to do that right now.  There's been talk of
> making triggers run "setuid" to the creating user, but it's not been
> done AFAIR.


--Brian

[SQL] I get an error with Foreign Keys

[Brian Powell]

I have around 40 tables defined, many relying on data in others so I have
foreign key constraints.  However, whenever I try to delete from any table,
I get:

ERROR:  SPI_execp() failed in RI_FKey_cascade_del()

What generates this?  I need assistance in knowing where to begin.  I
checked again, and I could not find any circular foreign key constraints...

Any help on where to search is appreciated.

Thanks,

--Brian

[SQL] 7.1.1 Lock Problems on Views

[Brian Powell]

Greetings,

I am investigating whether our application will run on 7.1.1 (from 
7.0.3), and our client software only accesses the database through views 
for security and convenience.

In our client app, we lock our table before doing something to it.  
Fortunately, in 7.0.3, a view looked like a table and we could perform:

lock v_whatever in share row exclusive mode;

However, in 7.1.1, it comes back and tells me that:

ERROR:  LOCK TABLE: v_whatever is not a table

The HUGE advantage in 7.0.3 was that we could present our db through 
views and the client didn't have to know.

I tried to put the lock statements into the rule of each view when the 
rule does the actual work, such as:

create rule v_whatever_insert as on insert to v_whatever
   do instead (
 lock whatever in share row exclusive mode;
 insert into whatever (blah) values (new.blah);
   );

However, the view won't be created, it claims there is a syntax error.

So, with 7.1.1, how can I create views and lock the data when modifying 
the table?

Thanks,

--Brian

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] 7.1.1 Lock Problems on Views

[Brian Powell]

On Thursday, May 31, 2001, at 10:42 AM, Tom Lane wrote:

> Why don't you redesign the app to not use table-level locks?
> An MVCC-aware app should have little or no need for table-level
> locking.
>

Thanks, I'll read up on MVCC in the docs.  While digging around, I came 
across the MVCC and it was the first I have heard of it (I haven't been 
reading up on the new features of 7.1)...

--Brian

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] 7.1 LOCK TABLE changes

[Brian Powell]

Greetings,

I am investigating whether our application will run on 7.1.1 (from 
7.0.3), and our client software only accesses the database through views 
for security and convenience.

In our client app, we lock our table before doing something to it.  
Fortunately, in 7.0.3, a view looked like a table and we could perform:

lock v_whatever in share row exclusive mode;

However, in 7.1.1, it comes back and tells me that:

ERROR:  LOCK TABLE: v_whatever is not a table

The HUGE advantage in 7.0.3 was that we could present our db through 
views and the client didn't have to know.

So, in 7.1.1, do I need to move the LOCK TABLE statements from the 
client software and into the rules of the views?

Thanks,

--Brian

---(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

[SQL] creating rules on joined views

[Brian Powell]

D'oh sorry for the subscribe message to the list...

Greetings, I am new to PostGreSQL and the advanced capabilities that it
offers.  I would like to create a joined view that allows inserts and
updates on two different tables (but, joined twice).

Suppose you have:


create table user {
  name  varchar(40) not null,
  email varchar(80),
  address_idint4 not null,
  shipping_address_id   int4 not null
};

create table addresses {
  address_idint4 primary key default nextval('address_seq_id'),
  address   varchar(80),
  city  varchar(80)
};

create view v_users as
select u.name
  , u.email
  , a1.address
  , a1.city
  , a2.address as ship_address
  , a2.city as ship_city
from user u
  , addresses a1
  , addresses a2
where u.address_id = a1.address_id
  and u.shipping_address_id = a2.address_id


Now, how would I create an insert rule on v_users because I have to insert
two addresses first, retrieve their id's and insert those into the user
table.  Conversely, an update introduces its own challenges as well.  If
someone could show me these pieces, I have pretty much figured out
everything else I need to do with the foreign keys, etc.  It seems doable
easily with pgpsql; however, I cannot use that for a rule, correct?

Thanks,
Brian

[SQL] Help with inserts into Views

[Brian Powell]

Greetings,

I am having difficulty creating a way to insert into a view which joins
multiple tables (specifically a view which joins multiple records from a
single table to a record in another table).

Please see the enclosed simple example for how I am currently inserting
(updating, etc.) on views.  However, When I get more complex tables with
multiple joins, this method will not work because a plpgsql function can
accept only 16 arguments.  I have tried using the NEW variable as the
argument to the function; however, this does not seem to work.

No matter what I do to create an insert trigger on the view, it never seems
to fire the trigger.

Any help and suggestions on how to perform an insert into multiple tables
from a single joined view would be greatly appreciated.

Thank you,
Brian

Example:

drop sequence addr_id_seq;
drop sequence member_id_seq;

drop rule v_member_insert;
drop function member_insert(varchar, varchar, varchar, varchar, varchar,
varchar, varchar);
drop view v_member;
drop table member;
drop table addr;


create sequence addr_id_seq;
create table addr (
   addr_idint4 primary key default nextval('addr_id_seq'),
   street varchar(40) not null,
   city   varchar(40) not null,
   state  varchar(40) not null
);

create sequence member_id_seq;
create table member (
   member_idint4 primary key default nextval('addr_id_seq'),
   username varchar(40) not null,
   address_id   int4 not null,
   shipping_id  int4 not null
);


create view v_member as
  select m.member_id, m.username, a.street, a.city, a.state,
 s.street as ship_street, s.city as ship_city,
 s.state as ship_state
  from member m, addr a, addr s
  where m.address_id = a.addr_id and m.shipping_id = s.addr_id;


create function member_insert(varchar, varchar, varchar, varchar,
varchar, varchar, varchar) returns text as '
declare
  my_address_id integer;
  my_shipping_id integer;

  my_username ALIAS FOR $1;
  my_street ALIAS FOR $2;
  my_city ALIAS FOR $3;
  my_state ALIAS FOR $4;
  my_ship_street ALIAS FOR $5;
  my_ship_city ALIAS FOR $6;
  my_ship_state ALIAS FOR $7;

begin
  my_address_id := nextval(''addr_id_seq'');
  insert into addr
(addr_id, street, city, state)
values (my_address_id, my_street, my_city, my_state);

  my_shipping_id := nextval(''addr_id_seq'');
  insert into addr
(addr_id, street, city, state)
values (my_shipping_id, my_ship_street, my_ship_city,
my_ship_state);

  insert into member (username, address_id, shipping_id)
values (my_username, my_address_id, my_shipping_id);

   return ''Success'';
end;
' language 'plpgsql';


CREATE RULE v_member_insert AS
ON INSERT TO v_member
DO INSTEAD
  
  SELECT member_insert(new.username, new.street, new.city, new.state,
new.ship_street, new.ship_city, new.ship_state);



-- Should create an error
 insert into v_member (username) values ('bob');

-- Should create a record
insert into v_member
  (username, street, city, state, ship_street, ship_city, ship_state)
  values ('bob', '123 Main', 'Denver', 'CO', '543 Elm', 'Buttland',
'MS');

select * from member;
select * from addr;
select * from v_member;

[SQL] Backup of BLOBS

[Brian Powell]

Greetings,

I have a database that will be quite large that must be backed up nightly.
I would like to use pg_dump; however, the problem is that we store binary
data as well.  Is there a way to backup this up without having to write a
program to do it?

Thanks,
Brian

[SQL] Bug in to_char()

[Brian Powell]

Greetings,

Working with PostGreSQL 7.02, I found the following problem:

The AM/PM designator in the to_char function does not work proper for 13:00
and 12:00.

See the following:

test=> select to_char('3-12-2000 14:00'::timestamp, 'Dy, HH12:MI PM');
to_char
---
 Sun, 02:00 PM
(1 row)


This is correct.


test=> select to_char('3-12-2000 8:00'::timestamp, 'Dy, HH12:MI PM');
to_char
---
 Sun, 08:00 AM
(1 row)

This is correct.

test=> select to_char('3-12-2000 13:00'::timestamp, 'Dy, HH12:MI PM');
to_char
---
 Sun, 01:00 AM
(1 row)

NO!  This is incorrect

test=> select to_char('3-12-2000 1:00'::timestamp, 'Dy, HH12:MI PM');
to_char
---
 Sun, 01:00 AM
(1 row)

This is correctly 1 am.

nicklebys=> select to_char('3-12-2000 12:00'::timestamp, 'Dy, HH12:MI PM');
to_char
---
 Sun, 12:00 AM
(1 row)

NO!  This is 12:00 pm.  0:00 or 24:00 is 12:00 am.

Any known work arounds or bug fixes planned?

Thanks,
Brian

-- 
   +---+
   |  Brian Powell, President [EMAIL PROTECTED]  |
   |  Filo Group  www.filogroup.com|
   |  One Broadway, Suite 300AAIM: filogroupbrian  |
   |  Denver, CO  80203   ICQ: 75037370|
   |  303.733.3248 office 303.733.7122 fax |
   +---+