Relation does not exist

whizyrel commented 3 years ago

I have two sql files in /docker-entrypoint-initdb.d/, one of which contains just entities. statements where executed first successfully. The other contains functions for triggers, these could not execute successfully because relations (peviously created) referred to in these functions do not exist. The filenames are in the correct alphabetical order a-z, entities to trigger functions; entities.sql, triggers.sql. Thank you

tianon commented 3 years ago

I'm not confident that I could even attempt reproducing with just the information provided -- can you please create a simplified example (perhaps a single of the entities you're referring to and a single simple function that references them) so we can try to reproduce?

whizyrel commented 3 years ago

Okay.

-- entities.setup.sql
CREATE TABLE IF NOT EXISTS entity_tests (
   id UUID DEFAULT uuid_generate_v4()
);

-- triggers.setup.sql
CREATE OR REPLACE FUNCTION entity_test_changes()
   RETURNS trigger AS $$
   BEGIN
      RETURN NEW;
   END;
   $$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS entity_test_changed ON entity_tests;

CREATE TRIGGER entity_test_changed AFTER INSERT OR UPDATE OR DELETE ON entity_tests FOR EACH ROW EXECUTE PROCEDURE entity_test_changes();

tianon commented 3 years ago

I guess I'm still missing something. :confused:

I've copied those files verbatim (with the provided names), and added the following simple Dockerfile:

FROM postgres:13
COPY *.sql /docker-entrypoint-initdb.d/

Then I did docker build --pull . and ran the result:

$ docker build --pull .
Sending build context to Docker daemon  4.096kB
Step 1/2 : FROM postgres:13
13: Pulling from library/postgres
Digest: sha256:117c3ea384ce21421541515edfb11f2997b2c853d4fdd58a455b77664c1adc20
Status: Image is up to date for postgres:13
 ---> 293e4ed402ba
Step 2/2 : COPY *.sql /docker-entrypoint-initdb.d/
 ---> 8bda62754a5b
Successfully built 8bda62754a5b

$ docker run -it --rm --env POSTGRES_PASSWORD=example 8bda62754a5b
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgresql/data -l logfile start

waiting for server to start....2021-06-08 18:41:09.206 UTC [47] LOG:  starting PostgreSQL 13.3 (Debian 13.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2021-06-08 18:41:09.208 UTC [47] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-06-08 18:41:09.213 UTC [48] LOG:  database system was shut down at 2021-06-08 18:41:09 UTC
2021-06-08 18:41:09.217 UTC [47] LOG:  database system is ready to accept connections
 done
server started

/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/entities.setup.sql
2021-06-08 18:41:09.386 UTC [73] ERROR:  function uuid_generate_v4() does not exist at character 62
2021-06-08 18:41:09.386 UTC [73] HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
2021-06-08 18:41:09.386 UTC [73] STATEMENT:  CREATE TABLE IF NOT EXISTS entity_tests (
       id UUID DEFAULT uuid_generate_v4()
    );
psql:/docker-entrypoint-initdb.d/entities.setup.sql:3: ERROR:  function uuid_generate_v4() does not exist
LINE 2:    id UUID DEFAULT uuid_generate_v4()
                           ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

After adding create.sql with CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; in it, I get the following instead:

$ docker run -it --rm --env POSTGRES_PASSWORD=example f0d867e70dbd
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgresql/data -l logfile start

waiting for server to start....2021-06-08 18:43:21.368 UTC [47] LOG:  starting PostgreSQL 13.3 (Debian 13.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2021-06-08 18:43:21.369 UTC [47] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-06-08 18:43:21.374 UTC [48] LOG:  database system was shut down at 2021-06-08 18:43:21 UTC
2021-06-08 18:43:21.377 UTC [47] LOG:  database system is ready to accept connections
 done
server started

/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/create-uuid.sql
CREATE EXTENSION

/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/entities.setup.sql
CREATE TABLE

/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/triggers.setup.sql
CREATE FUNCTION
psql:/docker-entrypoint-initdb.d/triggers.setup.sql:8: NOTICE:  trigger "entity_test_changed" for relation "entity_tests" does not exist, skipping
DROP TRIGGER
CREATE TRIGGER

waiting for server to shut down....2021-06-08 18:43:21.600 UTC [47] LOG:  received fast shutdown request
2021-06-08 18:43:21.601 UTC [47] LOG:  aborting any active transactions
2021-06-08 18:43:21.603 UTC [47] LOG:  background worker "logical replication launcher" (PID 54) exited with exit code 1
2021-06-08 18:43:21.603 UTC [49] LOG:  shutting down
2021-06-08 18:43:21.620 UTC [47] LOG:  database system is shut down
 done
server stopped

PostgreSQL init process complete; ready for start up.

2021-06-08 18:43:21.716 UTC [1] LOG:  starting PostgreSQL 13.3 (Debian 13.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2021-06-08 18:43:21.716 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2021-06-08 18:43:21.716 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2021-06-08 18:43:21.719 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-06-08 18:43:21.724 UTC [93] LOG:  database system was shut down at 2021-06-08 18:43:21 UTC
2021-06-08 18:43:21.728 UTC [1] LOG:  database system is ready to accept connections

I'm assuming psql:/docker-entrypoint-initdb.d/triggers.setup.sql:8: NOTICE: trigger "entity_test_changed" for relation "entity_tests" does not exist, skipping is the message you're referring to?

tianon commented 3 years ago

There's a minor typo - entity_test_changes vs entity_test_changed (note the s vs d).

tianon commented 3 years ago

Oh, that's not a typo -- the trigger doesn't exist, that's function name vs trigger name (just very confusingly similar). I think this is working correctly?

whizyrel commented 3 years ago

This is long and yes 👇🏾

I guess I'm still missing something.

I've copied those files verbatim (with the provided names), and added the following simple Dockerfile:

FROM postgres:13
COPY *.sql /docker-entrypoint-initdb.d/

Then I did docker build --pull . and ran the result:

$ docker build --pull .
Sending build context to Docker daemon  4.096kB
Step 1/2 : FROM postgres:13
13: Pulling from library/postgres
Digest: sha256:117c3ea384ce21421541515edfb11f2997b2c853d4fdd58a455b77664c1adc20
Status: Image is up to date for postgres:13
 ---> 293e4ed402ba
Step 2/2 : COPY *.sql /docker-entrypoint-initdb.d/
 ---> 8bda62754a5b
Successfully built 8bda62754a5b

$ docker run -it --rm --env POSTGRES_PASSWORD=example 8bda62754a5b
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgresql/data -l logfile start

waiting for server to start....2021-06-08 18:41:09.206 UTC [47] LOG:  starting PostgreSQL 13.3 (Debian 13.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2021-06-08 18:41:09.208 UTC [47] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-06-08 18:41:09.213 UTC [48] LOG:  database system was shut down at 2021-06-08 18:41:09 UTC
2021-06-08 18:41:09.217 UTC [47] LOG:  database system is ready to accept connections
 done
server started

/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/entities.setup.sql
2021-06-08 18:41:09.386 UTC [73] ERROR:  function uuid_generate_v4() does not exist at character 62
2021-06-08 18:41:09.386 UTC [73] HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
2021-06-08 18:41:09.386 UTC [73] STATEMENT:  CREATE TABLE IF NOT EXISTS entity_tests (
     id UUID DEFAULT uuid_generate_v4()
  );
psql:/docker-entrypoint-initdb.d/entities.setup.sql:3: ERROR:  function uuid_generate_v4() does not exist
LINE 2:    id UUID DEFAULT uuid_generate_v4()
                           ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

After adding create.sql with CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; in it, I get the following instead:

$ docker run -it --rm --env POSTGRES_PASSWORD=example f0d867e70dbd
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgresql/data -l logfile start

waiting for server to start....2021-06-08 18:43:21.368 UTC [47] LOG:  starting PostgreSQL 13.3 (Debian 13.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2021-06-08 18:43:21.369 UTC [47] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-06-08 18:43:21.374 UTC [48] LOG:  database system was shut down at 2021-06-08 18:43:21 UTC
2021-06-08 18:43:21.377 UTC [47] LOG:  database system is ready to accept connections
 done
server started

/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/create-uuid.sql
CREATE EXTENSION

/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/entities.setup.sql
CREATE TABLE

/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/triggers.setup.sql
CREATE FUNCTION
psql:/docker-entrypoint-initdb.d/triggers.setup.sql:8: NOTICE:  trigger "entity_test_changed" for relation "entity_tests" does not exist, skipping
DROP TRIGGER
CREATE TRIGGER

waiting for server to shut down....2021-06-08 18:43:21.600 UTC [47] LOG:  received fast shutdown request
2021-06-08 18:43:21.601 UTC [47] LOG:  aborting any active transactions
2021-06-08 18:43:21.603 UTC [47] LOG:  background worker "logical replication launcher" (PID 54) exited with exit code 1
2021-06-08 18:43:21.603 UTC [49] LOG:  shutting down
2021-06-08 18:43:21.620 UTC [47] LOG:  database system is shut down
 done
server stopped

PostgreSQL init process complete; ready for start up.

2021-06-08 18:43:21.716 UTC [1] LOG:  starting PostgreSQL 13.3 (Debian 13.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2021-06-08 18:43:21.716 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2021-06-08 18:43:21.716 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2021-06-08 18:43:21.719 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-06-08 18:43:21.724 UTC [93] LOG:  database system was shut down at 2021-06-08 18:43:21 UTC
2021-06-08 18:43:21.728 UTC [1] LOG:  database system is ready to accept connections

I'm assuming psql:/docker-entrypoint-initdb.d/triggers.setup.sql:8: NOTICE: trigger "entity_test_changed" for relation "entity_tests" does not exist, skipping is the message you're referring to?

whizyrel commented 3 years ago

Oh, that's not a typo -- the trigger doesn't exist, that's function name vs trigger name (just very confusingly similar). I think this is working correctly?

Yes, that is it.

wglambert commented 3 years ago

Closing since this is resolved

whizyrel commented 3 years ago

How? It isn't resolved!

wglambert commented 3 years ago

Ah, when you said "This is long and yes 👇🏾" to Tianon's "I think this is working correctly?" it actually was not?

With what's shown here https://github.com/docker-library/postgres/issues/855#issuecomment-857007713 there isn't any error reproduced and it seems like it's working correctly so I'd suggest asking over at the Docker Community Forums, Docker Community Slack, or Stack Overflow. Since these repos aren't really a user-help forum

whizyrel commented 3 years ago

This is not some user help. It is an actual 'something is not working correctly' and is docker-postgres related @tianon @wglambert

tianon commented 3 years ago

The main problem is that we're not clear on what is not working correctly -- what I tested appears to be working exactly as it should. The command is DROP TRIGGER IF EXISTS and the trigger doesn't exist yet so it doesn't have anything to drop.

It would also be helpful if you could try reproducing in a fresh non-Docker installation of PostgreSQL, because even if something is not working properly here, it's very very unlikely that it's related to the Dockerization of PostgreSQL, and is probably something more fundamental to PostgreSQL itself.

imme-emosol commented 2 years ago

For me this issue was caused by loosing the database-connection between the files.


\connect <database>
``

Which makes sense, as each script is executed separately from the command-line.

whizyrel commented 2 years ago

For me this issue was caused by loosing the database-connection between the files.
\connect <database>
``

Which makes sense, as each script is executed separately from the command-line.

Sounds interesting, imma check that out too. Thanks

docker-library / postgres

Relation does not exist #855