Auto commit Off how will it effect us ?

Dear all ,

Permit me to gain some of your most valuable knowledge ...........

Our intrAnet server has the following spec
1. RH 9.0
2. Postgresql 7.3.4
3. PHP 4.3.3
4. GCC 3.2.2

Till date my front end language (PHP) use to simply pass the data to
postgresql in case of insert and
update we were not using
1 BEGIN COMMIT block in PHP till date thanks to auto-commit-ON in postgreql
2. ROLLBACK script in PHP script


And now when our company has decided to switch over to postgresql 7.4
we have some doubt
1. Do we have to include BEGIN and COMMIT block around the insert
process . (Because Postgresql 7.4 has auto-commit-OFF) in our PHP scripts
2. Not only this do we also have to port the pgSQL functions which use
to do inserts into a third table such that
data gets committed on function execution


Please provide us any kinda information so that we can happily switch to
postgressql 7.4



Regards,
V Kashyap






---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Auto commit Off how will it effect us ?

hi,

SaiHertz And Control Systems wrote, On 12/3/2003 5:33 PM:
> Dear all ,
>
> Permit me to gain some of your most valuable knowledge ...........
>
> Our intrAnet server has the following spec
> 1. RH 9.0
> 2. Postgresql 7.3.4
> 3. PHP 4.3.3
> 4. GCC 3.2.2
>
> Till date my front end language (PHP) use to simply pass the data to
> postgresql in case of insert and
> update we were not using
> 1 BEGIN COMMIT block in PHP till date thanks to auto-commit-ON in postgreql
> 2. ROLLBACK script in PHP script
>
>
> And now when our company has decided to switch over to postgresql 7.4
> we have some doubt
> 1. Do we have to include BEGIN and COMMIT block around the insert
> process . (Because Postgresql 7.4 has auto-commit-OFF) in our PHP scripts

in pg7.4
AUTOCOMMIT = 'on'

try: SET AUTOCOMMIT = off; you will get:
ERROR: SET AUTOCOMMIT TO OFF is no longer supported

C.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Selecting from two tables

I have a table called Jobs and another called Applications. I can
query all Job rows, but how do I also include the number of Application
rows for each Job row in the SELECT statement?

applications has a jobid foreign key

create table job ( jobid SERIAL,
login varchar(40),
jobtitle varchar(70),
jobdescrip TEXT,
jobkeywords TEXT,
valid int2 NOT NULL,
postdate TIMESTAMPTZ,
PRIMARY KEY (jobid),
FOREIGN KEY(login) REFERENCES company
ON DELETE CASCADE
);


CREATE TABLE application ( applid SERIAL,
jobid INTEGER,
login varchar(6),
apldate TIMESTAMPTZ,
PRIMARY KEY (applid),
FOREIGN KEY (jobid) REFERENCES job
ON DELETE CASCADE,
FOREIGN KEY (login) REFERENCES
student
ON DELETE CASCADE
);



thanks for any help on this..
Michael


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: Selecting from two tables

>> I have a table called Jobs and another called Applications.
>> I can query all Job rows, but how do I also include the number
>> of Application rows for each Job row in the SELECT statement?

Try this way:

(1) CREATE VIEW viewappl AS
SELECT jobid
,count(*) as numappl
FROM application
GROUP BY
jobid;

(2) SELECT
job.jobid
,job.login
,job.jobtitle
,job.jobdescrip
,job.jobkeywords
,job.valid
,job.postdate
,viewappl.numappl
FROM job , viewappl
where job.jobid = viewappl.jobid
order by
job.jobid
;

I hope it's ok.

Hi, Mauro


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: Selecting from two tables

> -----Original Message-----
> From: pgsql-php-owner@postgresql.org [mailto:pgsql-php-
> owner@postgresql.org] On Behalf Of Michael Hanna
> Sent: 14 January 2004 23:33
> To: pgsql-php@postgresql.org
> Subject: [PHP] Selecting from two tables
>
> I have a table called Jobs and another called Applications. I can
> query all Job rows, but how do I also include the number of
Application
> rows for each Job row in the SELECT statement?
>
> applications has a jobid foreign key
>
> create table job ( jobid SERIAL,
> login varchar(40),
> jobtitle varchar(70),
> jobdescrip TEXT,
> jobkeywords TEXT,
> valid int2 NOT NULL,
> postdate TIMESTAMPTZ,
> PRIMARY KEY (jobid),
> FOREIGN KEY(login) REFERENCES company
> ON DELETE CASCADE
> );
>
>
> CREATE TABLE application ( applid SERIAL,
> jobid INTEGER,
> login varchar(6),
> apldate TIMESTAMPTZ,
> PRIMARY KEY (applid),
> FOREIGN KEY (jobid) REFERENCES
job
> ON DELETE CASCADE,
> FOREIGN KEY (login) REFERENCES
> student
> ON DELETE CASCADE
> );
>
>

How about using a sub-select.

SELECT t1.*, (SELECT COUNT(*) FROM application s1 WHERE
s1.jobid=t1.jobid) AS app_count FROM job t1;


Nick




---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match