PostgreSQL Function Execution issues

Question

I am facing a problem while executing a Function written using PL/pgSQL. The problem that I am facing is that when I execute the function manually using pgAdmin, the functions gets executed and the data gets loaded into the destination table. But when I execute the same function using a scheduler which has been developed by our team to execute all the different kinds of functions and ETLs based on the requirements, the postgreSQL function executes but doesn't COMMIT the data into the table. If we give a COMMIT statement after the function execution statement, only then the data gets committed.

Can any one suggest me what could be the possible reason for this behaviour of the function which get committed while executing manually but not when executed in a scheduler?

I would be highly obliged if someone can help me with this at the earliest.

Thanks in advance.

harmic · Accepted Answer · 2014-04-29 05:34:42Z

4

By default, postgresql operates in autocommit mode. That means that each SQL statement you execute Is immediately committed. This is what happens when you execute command using tools such as pgadmin or psql, assuming you do not execute BEGIN first.

I am guessing your scheduler framework is executing BEGIN before starting your job and that it expects you to commit the work as part of the job.

Without knowing more details about the scheduler it is impossible to be more specific.

answered Apr 29, 2014 at 5:34

harmic

31k6 gold badges75 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Yousuf Sultan Over a year ago

I am passing this statement as a parameter to my scheduler which calls my PostgreSQL function. SELECT test();commit; There is no BEGIN statement. If I remove the commit; statement then the data does not get inserted into the table, but if I give that commit it inserts into it.

harmic Over a year ago

@YousufIbnAkhtarSultan for that to happen there must have been a BEGIN given on that database handle before your task gets run in the scheduler - maybe by a previous task? I cannot think of any other explanation. If you can enable statement logging then you will probably be able to see what is going on a little better.

Collectives™ on Stack Overflow

PostgreSQL Function Execution issues

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related