Re: FW: Undelivered Mail Returned to Sender

[David Rowley]

On Sun, 11 Aug 2019 at 06:53, stan  wrote:
>
> I apologize for asking, what I suspect will turn out to be a newbie
> question, but I have managed to get myself quite confused on this.
>
> I am defining a view as follows
>
>
> CREATE OR REPLACE view purchase_view as
> select
> project.proj_no ,
> qty ,
> mfg_part.mfg_part_no ,
> mfg.name as m_name ,
> mfg_part.descrip as description ,
> (
> SELECT
> name
> FROM
> vendor
> WHERE
> bom_item.vendor_key =
> (
> SELECT
> vendor_key
> FROM
> mfg_vendor_relationship
> WHERE
> bom_item.mfg_key = mfg_key
> AND
> prefered = TRUE
> AND
> bom_item.project_key = project_key
>
> )
> )
> as v_name ,

> ERROR:  more than one row returned by a subquery used as an expression
>
> Can someone please enlighten me as to the error of my ways?

Looks to me like your WHERE clause is wrong in the subquery. "WHERE
bom_item.vendor_key ="  surely that should be just "WHERE vendor_key =
" (assuming that's the primary key column of the vendor table).

Also, you've mentioned you've only a single record in the
mfg_vendor_relationship, so the error can't be due to multiple records
matching in the mfg_vendor_relationship table. However, given the
unique constraint on that table includes 3 columns and you're just
filtering on 2 of them, then it would only take some rows in there
with the same mfg_key and project_key values but a different
vendor_key to get the same error from that part of the query.  If that
shouldn't happen, then perhaps your UNIQUE constraint should not
contain the vendor_key column.  You'd have to explain what you need in
more detail for someone to be able to help you fix that.

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Quoting style (was: Bulk Inserts)

[Peter J. Holzer]

On 2019-08-10 21:01:50 -0600, Rob Sargent wrote:
> On Aug 10, 2019, at 8:47 PM, Souvik Bhattacherjee 
> wrote:
> 
> Hi Adrian,
> 
> Thanks for the response.
> 
> > Yes, but you will some code via client or function that batches the 
> > inserts for you.
> 
> Could you please elaborate a bit on how EXP 1 could be performed such that
> it uses bulk inserts?
> 
> Best,
> -SB
> 
> On Fri, Aug 9, 2019 at 7:26 PM Adrian Klaver 
> wrote:
> 
[70 lines of full quote removed]


> Top-posting (i.e. putting your reply at the top is discouraged here)

He didn't really top-post. He quoted the relevant part of Adrian's
posting and then wrote his reply below that. This is the style I prefer,
because it makes it really clear what one is replying to.

After his reply, he quoted Adrian's posting again, this time completely.
I think this is unnecessary and confusing (you apparently didn't even
see that he quoted something above his reply). But it's not as bad as
quoting everything below the answer (or - as you did - quoting
everything before the answer which I think is even worse: If I don't see
any original content within the first 100 lines or so I usually skip the
rest).

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: PGP signature

Re: Probably a newbie question

[stan]

On Sat, Aug 10, 2019 at 02:57:14PM -0400, stan wrote:
> Sorry, I got the list address wrong the first time, and when I corrected it,
> I forget to fix the subject line.
> 
> I apologize for asking, what I suspect will turn out to be a newbie
> question, but I have managed to get myself quite confused on this.
> 
> I am defining a view as follows
> 
> 
> CREATE OR REPLACE view purchase_view as 
> select 
>   project.proj_no ,
>   qty ,
>   mfg_part.mfg_part_no ,
>   mfg.name as m_name ,
>   mfg_part.descrip as description ,
>   ( 
>   SELECT 
>   name
>   FROM
>   vendor
>   WHERE
>   bom_item.vendor_key =
>   (
>   SELECT
>   vendor_key
>   FROM
>   mfg_vendor_relationship
>   WHERE
>   bom_item.mfg_key = mfg_key
>   AND
>   prefered = TRUE
>   AND
>   bom_item.project_key = project_key
>   
>   )
>   ) 
>   as v_name ,
>   /*
>   vendor.name as v_name ,
>   */
>   cost_per_unit ,
>   costing_unit.unit,
>   need_date ,
>   order_date ,
>   recieved_date ,
>   po_no ,
>   po_line_item 
> from 
>   bom_item
> right join project on 
>   project.project_key = bom_item.project_key
> inner join mfg_part on 
>   mfg_part.mfg_part_key = bom_item.mfg_part_key
> inner join vendor on 
>   vendor.vendor_key = bom_item.vendor_key
> inner join costing_unit on 
>   costing_unit.costing_unit_key = bom_item.costing_unit_key
> inner join mfg on 
>   mfg.mfg_key = bom_item.mfg_key 
> WHERE bom_item is NOT NULL  
> ORDER BY 
>   project.proj_no ,
>   mfg_part
>   ;
> 

Thanks to the kind, bright people on this list, I have solved my problem.
The basic issue was that my from clause was on the wrong table.

Thanks to everyone who spent their time helping me out on this!
-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

A question about trigger fucntion syntax

[stan]

Good morning (at least is is morning East Coast USA time).

I am trying to create a function to validate an attempted record 
insert, and I am having a hard time coming up with syntax that
is acceptable.

Here is the scenario I have a table that has (among other items) employee_key
and work_type_key (both integer FOREIGN KEYS). Then I have another table
that has the following structure:

CREATE  TABLE permitted_work (
employee_key   integer ,
work_type_key  integer ,
permit boolean DEFAULT FALSE NOT NULL ,
modtime timestamptz DEFAULT current_timestamp ,
FOREIGN KEY (employee_key) references
employee(employee_key) ,
FOREIGN KEY (work_type_key) references
work_type(work_type_key) ,
CONSTRAINT permit_constraint UNIQUE
(employee_key , work_type_key)
);

 What I think I need to do is create a function that is fired on an insert,
 or update to the 1st table that verifies that there is an existing row in
 permitted_work that matches the combination of employee_key AND
 work_type_key AND has the value TRUE in the permit column.

 First does this seem to be a good way to achieve this constraint? If not,
 I am open to suggestions as to other ways to address this  requirement. 

 If it does, could someone give me a little help with th syntax of the
 needed function ??

 Thanks for your time helping me with this.



-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Re: A question about trigger fucntion syntax

[stan]

On Sun, Aug 11, 2019 at 08:56:13AM -0400, stan wrote:
> Good morning (at least is is morning East Coast USA time).
> 
> I am trying to create a function to validate an attempted record 
> insert, and I am having a hard time coming up with syntax that
> is acceptable.
> 
> Here is the scenario I have a table that has (among other items) employee_key
> and work_type_key (both integer FOREIGN KEYS). Then I have another table
> that has the following structure:
> 
> CREATE  TABLE permitted_work (
> employee_key   integer ,
>   work_type_key  integer ,
>   permit boolean DEFAULT FALSE NOT NULL ,
>   modtime timestamptz DEFAULT current_timestamp ,
>   FOREIGN KEY (employee_key) references
>   employee(employee_key) ,
>   FOREIGN KEY (work_type_key) references
>   work_type(work_type_key) ,
>   CONSTRAINT permit_constraint UNIQUE
>   (employee_key , work_type_key)
>   );
> 
>  What I think I need to do is create a function that is fired on an insert,
>  or update to the 1st table that verifies that there is an existing row in
>  permitted_work that matches the combination of employee_key AND
>  work_type_key AND has the value TRUE in the permit column.
> 
>  First does this seem to be a good way to achieve this constraint? If not,
>  I am open to suggestions as to other ways to address this  requirement. 
> 
>  If it does, could someone give me a little help with th syntax of the
>  needed function ??
> 
>  Thanks for your time helping me with this.

BTW, here is what I Ave tried.

CREATE OR REPLACE FUNCTION check_permission()
  RETURNS trigger AS
$BODY$
BEGIN
SELECT 
permit
FROM
permitted_work
WHERE
NEW.employee_key = OLD.employee_key
AND
NEW.work_type_key = OLD.work_type_key

   RETURN permit;
END;
$BODY$
LANGUAGE PLPGSQL;

and when I try to insert it I get a syntax error at the RETURN

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Re: A question about trigger fucntion syntax

[Pavel Stehule]

Hi


> BTW, here is what I Ave tried.
>
> CREATE OR REPLACE FUNCTION check_permission()
>   RETURNS trigger AS
> $BODY$
> BEGIN
> SELECT
> permit
> FROM
> permitted_work
> WHERE
> NEW.employee_key = OLD.employee_key
> AND
> NEW.work_type_key = OLD.work_type_key
>
>RETURN permit;
> END;
> $BODY$
> LANGUAGE PLPGSQL;
>
> and when I try to insert it I get a syntax error at the RETURN
>

there is more than one issue

1) trigger function should to returns record type (with same type like
table joined with trigger). Column permit is a boolean, so some is wrong.

2) the structure of your function is little bit strange. Probably you want
some like

CREATE OR REPLACE FUNCTION check_permission()
RETURNS trigger AS $$
DECLARE _permit boolean; -- variables should be declared;
BEGIN
  SELECT permit INTO _permit -- result should be assigned to variable
FROM permitted_work
   ...;
  IF NOT permit THEN
RAISE EXCEPTION 'some error message';
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Regards

Pavel


>
> --
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
> -- Benjamin Franklin
>
>
>

Re: Quoting style (was: Bulk Inserts)

[Rob Sargent]

Sorry.  I thought I had cut most of the redundancy 

> On Aug 11, 2019, at 2:26 AM, Peter J. Holzer  wrote:
> 
>> On 2019-08-10 21:01:50 -0600, Rob Sargent wrote:
>>On Aug 10, 2019, at 8:47 PM, Souvik Bhattacherjee 
>>wrote:
>> 
>>Hi Adrian,
>> 
>>Thanks for the response.
>> 
>>> Yes, but you will some code via client or function that batches the 
>>> inserts for you.
>> 
>>Could you please elaborate a bit on how EXP 1 could be performed such that
>>it uses bulk inserts?
>> 
>>Best,
>>-SB
>> 
>>On Fri, Aug 9, 2019 at 7:26 PM Adrian Klaver 
>>wrote:
>> 
> [70 lines of full quote removed]
> 
> 
>> Top-posting (i.e. putting your reply at the top is discouraged here)
> 
> He didn't really top-post. He quoted the relevant part of Adrian's
> posting and then wrote his reply below that. This is the style I prefer,
> because it makes it really clear what one is replying to.
> 
> After his reply, he quoted Adrian's posting again, this time completely.
> I think this is unnecessary and confusing (you apparently didn't even
> see that he quoted something above his reply). But it's not as bad as
> quoting everything below the answer (or - as you did - quoting
> everything before the answer which I think is even worse: If I don't see
> any original content within the first 100 lines or so I usually skip the
> rest).
> 
>hp
> 
> -- 
>   _  | Peter J. Holzer| we build much bigger, better disasters now
> |_|_) || because we have much more sophisticated
> | |   | h...@hjp.at | management tools.
> __/   | http://www.hjp.at/ | -- Ross Anderson 

Re: Bulk Inserts

[Adrian Klaver]

On 8/10/19 7:47 PM, Souvik Bhattacherjee wrote:

Hi Adrian,

Thanks for the response.

 > Yes, but you will some code via client or function that batches the
 > inserts for you.

Could you please elaborate a bit on how EXP 1 could be performed such 
that it uses bulk inserts?


I guess it comes down to what you define as bulk inserts. From your OP:

EXP 1: inserts with multiple txn:
insert into tab2 (attr1, attr2) (select attr1, attr2 from tab1 where 
attr2 = 10);
insert into tab2 (attr1, attr2) (select attr1, attr2 from tab1 where 
attr2 = 20);


If the selects are returning more then one row then you are already 
doing bulk inserts. If they are returning single rows or you want to 
batch them then you need some sort of code to do that. Something 
like(pseudo Python like code):


attr2_vals= [(10, 20, 30, 40), (50, 60, 70, 80)]

for val_batch in attr2_vals:
BEGIN
for id in val_batch:
insert into tab2 (attr1, attr2) (select attr1, attr2
from tab1 where attr2 = id)
COMMIT



Best,
-SB




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Recomended front ends?

[Peter J. Holzer]

On 2019-08-08 10:47:47 -0700, Rich Shepard wrote:
> On Thu, 8 Aug 2019, Adrian Klaver wrote:
> > Unfortunately it does not:
> > https://code.djangoproject.com/wiki/MultipleColumnPrimaryKeys
> > 
> > Given that the issue:
> > https://code.djangoproject.com/ticket/373
> > is 14 years old does not inspire confidence that it will change anytime 
> > soon.
> 
> Adrian,
> 
> That's really interesting. I don't see how a framework cannot implement
> multi-column PKs.

You are a database guy. You have a database and want to write an
application for it. Even in a greenfield project, you probably do the
database design first. (I know I do)

The Django framwork comes from the opposite direction. The programmers
aren't expected to know much about relational databases[1]. They are
expected to write their application and Django provides (among other
things like session and form handling) a persitence layer which happens
to be backed by a relational database.

So Django's ORM layer isn't intended to deal with any possible (or even
any reasonable) database model - just with database models generated by
Django. 

Django lets you use "unmanaged" tables, but it is quite noticeable that
this isn't the primary use case.

> Many databases I have include tables for samples (geochemical, biological,
> physical) where the PK for each row is location, date, parameter. Good thing
> I don't like browser user interfaces, eh? :-)

Django isn't the only web framework.

hp

[1] It should be noted, however, that the Django ORM is what Joel
Spolsky calls a leaky abstraction. If you are ignorant about how
RDBMSs work, your application will probably be horrendously slow.
Even if you do know this, you often have to bend over backwards to
get reasonable performance.

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: PGP signature

Re: A question about trigger fucntion syntax

[stan]

I trimmed this thread quite a bit.

Thanks to the help I have received, I am making progress. I have looked a the
recommended documentation, and I believe I am close. I have utilized the
"debugging printf" capability here. Here is where I am. I have the following
function create.


DROP TRIGGER validate_task_trig ON task_instance ;

DROP FUNCTION check_permission() ;


CREATE FUNCTION check_permission()
RETURNS trigger AS $$
DECLARE _permit boolean;
BEGIN
SELECT
permit INTO _permit
FROM
permitted_work
WHERE
NEW.employee_key = OLD.employee_key
AND
NEW.work_type_key = OLD.work_type_key;
RAISE NOTICE 'New employee_id % NEW.work_type_key % _permit = %', 
NEW.employee_key ,
NEW.work_type_key ,
_permit ;
if NOT _permit THEN
RAISE NOTICE 'No permission record';
RAISE EXCEPTION 'No permission record';
ELSE
RAISE NOTICE 'Found Permission Record';
END IF;
if _permit = FALSE THEN
RAISE NOTICE 'Permission Denied';
ELSE
RAISE NOTICE 'Permission Granted';
END IF;

return NEW;
END;
$$ 
LANGUAGE PLPGSQL;

CREATE TRIGGER validate_task_trig BEFORE INSERT OR UPDATE ON task_instance
FOR EACH ROW EXECUTE FUNCTION check_permission();


Now the issues, currently seems to be that nothing is getting assigned to
_permit. Here is the output of a run with 0 records in the permitted_work
table.

NOTICE:  New employee_id 1 NEW.work_type_key 8 _permit = 
NOTICE:  Found Permission Record
NOTICE:  Permission Granted
INSERT 0 1

so it appears that nothing is getting assigned to _permit. Also should I be
checking for _permit as NOT NULL in the first if clause?

Thanks for all the had holding on this. Brand new application for me.

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

[SOLVED] Re: A question about trigger fucntion syntax

[stan]

On Sun, Aug 11, 2019 at 05:31:13PM -0400, stan wrote:
> I trimmed this thread quite a bit.
> 
> Thanks to the help I have received, I am making progress. I have looked a the
> recommended documentation, and I believe I am close. I have utilized the
> "debugging printf" capability here. Here is where I am. I have the following
> function create.
> 
> 
> DROP TRIGGER validate_task_trig ON task_instance ;
> 
> DROP FUNCTION check_permission() ;
> 
> 
> CREATE FUNCTION check_permission()
> RETURNS trigger AS $$
> DECLARE _permit boolean;
> BEGIN
> SELECT
> permit INTO _permit
> FROM
> permitted_work
> WHERE
> NEW.employee_key = OLD.employee_key
> AND
> NEW.work_type_key = OLD.work_type_key;
> RAISE NOTICE 'New employee_id % NEW.work_type_key % _permit = %', 
>   NEW.employee_key ,
>   NEW.work_type_key ,
>   _permit ;
> if NOT _permit THEN
> RAISE NOTICE 'No permission record';
> RAISE EXCEPTION 'No permission record';
>   ELSE
> RAISE NOTICE 'Found Permission Record';
> END IF;
> if _permit = FALSE THEN
> RAISE NOTICE 'Permission Denied';
>   ELSE
> RAISE NOTICE 'Permission Granted';
> END IF;
> 
> return NEW;
> END;
> $$ 
> LANGUAGE PLPGSQL;
> 
> CREATE TRIGGER validate_task_trig BEFORE INSERT OR UPDATE ON task_instance
> FOR EACH ROW EXECUTE FUNCTION check_permission();
> 
> 
> Now the issues, currently seems to be that nothing is getting assigned to
> _permit. Here is the output of a run with 0 records in the permitted_work
> table.
> 
> NOTICE:  New employee_id 1 NEW.work_type_key 8 _permit = 
> NOTICE:  Found Permission Record
> NOTICE:  Permission Granted
> INSERT 0 1
> 
> so it appears that nothing is getting assigned to _permit. Also should I be
> checking for _permit as NOT NULL in the first if clause?
> 
> Thanks for all the had holding on this. Brand new application for me.

For the archive.

I have this working, Here is the function that I woulnd up with.


DROP TRIGGER validate_task_trig ON task_instance ;

DROP FUNCTION check_permission() ;


CREATE FUNCTION check_permission()
RETURNS trigger AS $$
DECLARE _permit boolean;
BEGIN
SELECT
permit INTO _permit
FROM
permitted_work
WHERE
NEW.employee_key = permitted_work.employee_key
AND
NEW.work_type_key = permitted_work.work_type_key;
if _permit IS NULL THEN
RAISE EXCEPTION 'No permission record';
ELSE
END IF;
if _permit = FALSE THEN
RAISE EXCEPTION 'Permisson Denied';
END IF;

return NEW;
END;
$$ 
LANGUAGE PLPGSQL;

CREATE TRIGGER validate_task_trig BEFORE INSERT OR UPDATE ON task_instance
FOR EACH ROW EXECUTE FUNCTION check_permission();

Thanks to all the people that were instrumental in helping me learn
triggers and functions.


-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Postgres Database Backup Size

[Shiwangini Shishulkar]

Hi,

Here , we have first tried with .BAK backups. For, 43GB database-> the
backup size was ~86GB.

Then, I configured backup with .sql file. Here it was ~70GB for the same
database size.

Next, now our database size keeps increasing.  As well as backup size is
also increasing. Now, backup size is more than 100GB for 50GB database
size. Due to some business requirement , we need to restore each backup
daily.

Here, I'm taking backup via below sample statement:

pg_dump -U postgres -d welcome -f welcome_${DATE}.sql

I had also tried with compressed backups via -Fc. But, since it is
compressed, in case of restoration -> first it decompresses the backup and
restore it. Which again takes a lot of time.

Any way to reduce backup size of postgres backups or it's default behavior
of postgres to take backups which are double in size?

Thanks,
Shiwangini