Different result when calling a stored procedure from pgadmin and from doctrine ORM

Question

I have the following stored procedure:

CREATE OR REPLACE FUNCTION get_next_network()
    RETURNS inet AS
$BODY$
/* get the next networkd */
DECLARE 
      ip inet;
BEGIN
      select into ip (inet'10.41.142.0' + nextval('NetworkAddress_seq'));
  return ip;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

the sequence NetworkAddress_seq increments with 256:

CREATE SEQUENCE "NetworkAddress_seq"
    INCREMENT 256
    MINVALUE 256
    MAXVALUE 9223372036854775807
    START 256
    CACHE 256;
    ALTER TABLE "NetworkAddress_seq"
    OWNER TO my_user;

When I call the function from pgAdmin I get: 10.41.143.0

When I call it from php/Doctrine/ORM:

$stmt = $this->_em->getConnection()->prepare("select get_next_network()");
$stmt->execute();
$stmt->fetchColumn(0);

I get something like 10.56.142.0

Why?

Edit: I checked that I'm using the same procedure by hardcoding the return value - in this case I get the same value both in pgadmin and in php. This also puts the question mark on the sequence but as I'm querying it from the procedure and not directly I would assume that no matter from where I call the get_next_network procedure it will use the same sequence... or am I wrong about this?

Edit2: The strange behavior is not related to the php side - I get the same strange results if open multiple pgAdmin SQL query windows (different DB connections?). For example: in Window1 I run

select * from get_next_network();

end get: 10.43.143.0 and if I run it again I get: 10.43.144.0, 10.43.145.0, ... etc. In Window2 - the same query returns: 10.44.143.0 and all the subsequent queries get: 10.44.144.0, 10.44.145.0,..etc.

score 2 · Answer 1 · answered Nov 08 '13 at 17:59

2

Situations like these usually point to that your are not connecting to the correct database.

answered Nov 08 '13 at 17:59

Marek

7,337
1
22
33

score 0 · Answer 2 · edited May 23 '17 at 12:11

0

Next to a possible confusion with the database connection like hinted by @Marek, there is another typical pitfall:

A different setting for the search_path, which could resolve 'NetworkAddress_seq' to a SEQUENCE of the same name in a different schema, or even find a function get_next_network() in a different schema.

  select into ip (inet'10.41.142.0' + nextval('NetworkAddress_seq'));

BTW, I never use CaMeL-case identifiers in Postgres. That's just asking for trouble.

The call nextval('NetworkAddress_seq') looks for a sequence with CaMeL-case name, created with:

CREATE SEQUENCE "NetworkAddress_seq";  -- with double-quotes!

But not:

CREATE SEQUENCE NetworkAddress_seq;

.. which results in a sequence named networkaddress_seq, and you would have to use:

nextval('networkaddress_seq')

May be part of the problem.

Also, everything might be correct and the sequence has been incremented that many times in between. You ruled that out, right?

edited May 23 '17 at 12:11

Community

1
1

answered Nov 08 '13 at 18:35

Erwin Brandstetter

605,456
145
1,078
1,228

I edited my question to clarify why I'm sure that I'm using the same get_next_network() function. Using a different sequence should be impossible as long as I'm not querying the sequence directly but from the procedure - and I would assume that no matter from where I call the procedure it will always use the same sequence. – tudor Nov 10 '13 at 09:33
@tudor: That's not correct: `Using a different sequence should be impossible`. Object names take the current `search_path` into account. You can save the `search_path` with the function, though. See point 5. of my [linked answer](http://stackoverflow.com/questions/9067335/how-to-create-table-inside-specific-schema-by-default-in-postgres/9067777#9067777). – Erwin Brandstetter Nov 10 '13 at 13:19

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

The problem was the CACHE setting to 256. I removed it (default 1) and now no matter from where I call my procedure I get the next value..

An explanation from here

In the case of PostgreSQL, a sequence object can be configured to cache multiple values within a single backend process so that if you are generating large numbers of values, a particular backend might " lay claim" to a range of 100 values all at once, making clashes between concurrent processes go away.

A disadvantage to cacheing is that values can and will get skipped along the way. If you were expecting them to fall in some exact serial order, that expectation will fail.

I was expecting that each call to the procedure will grow the sequence with the increment no matter from where it was executing.

Different result when calling a stored procedure from pgadmin and from doctrine ORM

3 Answers3