I have an SSIS sequence container that extracts data from individual source database tables into a Stage layer. However, I need to know when a given calculation crashes in order for the given error to be written to the configuration table, which is provided by the yellow highlighted task in which the procedure is called. The problem is that if a given container falls due to an error, it does not flow through failure precendence constraint into the given task (LogPackageFailed). I tried setting FaiPackageOnFailure together with FailParentOnFailure to TRUE in the properties, but it didn't help. The proposed solution in the following links does not work either: - http://techblog.elish.net/2010/12/ssis-foreach-loop-container-continue-on.html - https://simonworth.wordpress.com/2009/11/11/ssis-event-handler-variables-propagate/ , which was addressed in a previous similar query.

For serial columns used in Postgresql < 10, we manage the sequence by its name. We were able to reset a sequence with:

SELECT setval('table_id_seq', (SELECT MAX(id) FROM table));

From version 10, using identity columns, there is no need to use the sequence name. That's nice.

ALTER TABLE table ALTER COLUMN id RESTART WITH 1000;

How do I set the identity column to be the max(id) without knowing the sequence name? As far as I can see from the [ALTER TABLE syntax](https://www.postgresql.org/docs/current/sql-altertable.html) there is no way to have a subquery to compute the start of the sequence. I would like to write something like:

ALTER TABLE table ALTER COLUMN id RESTART WITH (SELECT MAX(id) FROM table);

Problem Statment: ----------------- We have multiple tables containing a column having the name "Code", it is an identity column. Our application is running by our multiple locations/clients, and they are using the same database. Due to this simultaneous access of a table, our identity columns are suffering from a gap for each client. Let's take an example: ---------------------- Client C1 creates a record and he gets the Code as '1', meanwhile, Client C2 creates a record and he gets the Code as '2' which is wrong. He should gets the Code as '1', this issue is causing because both clients are using the same table. Possible Solution: ------------------ We can use triggers to maintain custom sequence but in that case, we have to maintain trigger for each table which is tedious alternatively, We can use sequence. By taking of sequence is there any possibility that we make such a sequence that can take a function and this function can catch the table name internally without passing the table as a parameter. Or -- If there is any best solution that can solve this problem please recommend. Thanks

I need to get the definition of some objects in the SQL server. I got the definitions of stored procedures, functions, views and triggers from sys.sql_modules. But, I don't know how to get that of user-defined table types and of sequences. I am trying to create this in another database and automate this process. Does anyone know how to get the definition of table types and sequences?

I've meshed together a way to determine what the data_type is as in the data_type you use in the syntax when creating a new table based off of the PostgreSQL wiki page. If there is something wrong with my query I need to actually know *what* in a given scenario would throw it off on the explicit context of having a query or queries to run on a purely test database/table to modify that database/table so run this query on in order to test for any false-positives. SELECT pg_attribute.attname, format_type(pg_attribute.atttypid, pg_attribute.atttypmod), CASE WHEN format_type(pg_attribute.atttypid, pg_attribute.atttypmod)='bigint' THEN 'bigserial' WHEN format_type(pg_attribute.atttypid, pg_attribute.atttypmod)='integer' THEN 'serial' END AS type FROM pg_index, pg_class, pg_attribute WHERE pg_class.oid = 'delete2'::regclass AND indrelid = pg_class.oid AND pg_attribute.attrelid = pg_class.oid AND pg_attribute.attnum = any(pg_index.indkey) AND indisprimary; Here is a table with a primary key that does not return the primary key with this query: CREATE TABLE delete_key_bigserial ( test1 integer, id bigserial NOT NULL, col1 text, col2 text, test2 integer );

It looks like I messed up a database migration and while all sequences are there, they would start at 1, which triggers errors like: > django.db.utils.IntegrityError: duplicate key value violates unique > constraint "django_admin_log_pkey" DETAIL: Key (id)=(2) already > exists. Is there a query/script I could run that would run across all tables in the database, look at any columns tied to a sequence and reset those sequences to max(column) + 1? Using PostgreSQL v11.

I need to obtain multiple values from a sequence with only one query. On Stack Overflow I found this: https://stackoverflow.com/questions/896274/select-multiple-ids-from-a-postgresql-sequence The solution seems to be the following:

select nextval('my_sequence') from generate_series(1, N)

How does this behave when my application could launch this query concurrently each time it serves some client request? The top-voted answer has a comment that reads: > Note that the 3 numbres (nextvals) are not guaranteed to be sequential. I don't necessarily care about sequentiality, but is it guaranteed that the returned values are *at least* strictly monotonically increasing, even if they may be interweaved with the values generated by another concurrent query?

I'm using a PostgresSQL database as an eventstore. We used to use https://github.com/SQLStreamStore/SQLStreamStore But they had issues when having a lot of parallel transactions. Essentially we suffered from a lot of 'skipped' events. A similar problem is explained here: https://github.com/eugene-khyst/postgresql-event-sourcing?tab=readme-ov-file#transactional-outbox-using-transaction-id So together with a co-worker we decided to fork the library and implement it using [pg_current_snapshot()](https://pgpedia.info/p/pg_current_snapshot.html) . We had a few iterations of this but in the end we got it working: https://github.com/ArneSchoonvliet/SQLStreamStore So the main idea is, if we see a gap in between positions we will only trust the events with a lower transaction_id than 'xmin'. This has worked great for us. And most problems are solved. But sometimes we have a weird occurrence

Position    MessageId                               CreatedAt                       TransactionId
31170300	be7b412a-103c-5cdd-8458-57fbb0e5c39e	2024-09-29 13:23:27.733 +0200	2306832989
31170299	38b9d7d9-540c-5440-a2a0-10b91cffb2ad	2024-09-29 13:23:27.736 +0200	2306832990

Query result

Position: 31170297, Array index: 0, Transaction id: 2306832974
Position: 31170298, Array index: 1, Transaction id: 2306832976
Position: 31170300, Array index: 2, Transaction id: 2306832989
Xmin: 2306832990

In the query result you see that 31170299 is missing. So our 'gap checking' code kicks in. And will check if all transactions_ids are lower than xmin. In this case they are... 31170299 wasn't visible yet. So as a result that event will be skipped. **Question** Is it expected that this can happen. A newer transaction claiming a lower seq value? We are using Google Cloud managed pgsql db Since I don't really know how we would ever be able to detect that without checking every time if transactions are still happening. But this would impact performance since we would lose a lot of time with 'actual' gaps (caused by transactions that are rolled back) People probably wonder what the insert / query sql looks like INSERT: https://github.com/ArneSchoonvliet/SQLStreamStore/blob/master/src/SqlStreamStore.Postgres/PgSqlScripts/AppendToStream.sql Important part:

INSERT INTO __schema__.messages (message_id,
                                 stream_id_internal,
                                 stream_version,
                                 created_utc,
                                 type,
                                 json_data,
                                 json_metadata,
                                 transaction_id)
SELECT m.message_id, _stream_id_internal, _current_version + (row_number()
    over ()) :: int, _created_utc, m.type, m.json_data, m.json_metadata, pg_current_xact_id()
FROM unnest(_new_stream_messages) m
ON CONFLICT DO NOTHING;
GET DIAGNOSTICS _success = ROW_COUNT;

As you can see the position isn't set. This is because it's an autoincrement defined like this: "position" int8 DEFAULT nextval('messages_seq'::regclass) NOT NULL QUERY: https://github.com/ArneSchoonvliet/SQLStreamStore/blob/master/src/SqlStreamStore.Postgres/PgSqlScripts/ReadAll.sql Important part:

BEGIN
  OPEN _txinfo FOR
  SELECT pg_snapshot_xmin(pg_current_snapshot());
  RETURN NEXT _txinfo;
    
  OPEN _messages FOR
  WITH messages AS (
      SELECT __schema__.streams.id_original,
             __schema__.messages.message_id,
             __schema__.messages.stream_version,
             __schema__.messages.position,
             __schema__.messages.created_utc,
             __schema__.messages.type,
             __schema__.messages.transaction_id,
             __schema__.messages.json_metadata,
             __schema__.messages.json_data,
             __schema__.streams.max_age
      FROM __schema__.messages
             INNER JOIN __schema__.streams ON __schema__.messages.stream_id_internal = __schema__.streams.id_internal
      WHERE  __schema__.messages.position >= _position
      ORDER BY __schema__.messages.position
      LIMIT _count
  )
  SELECT * FROM messages LIMIT _count;
  RETURN NEXT _messages;
END;

Postgres 9.3 Debian 7.0 I created a specific schema for a specific user and created a view in this schema for this user, so it's the only table he knows that exists. The problem is that this same user needs usage on the sequence of the primary key of this table, but it says "ERROR: permission denied for sequence" The original table and its sequence belongs to schema A. This users's schema B has an insert-able view of this table T. I cannot grant usage on schema A for this user, otherwise he will be able to see the names and definition of all my tables. The question is: Is there some way to create some kind of view for this sequence so he can call nextval() and currval()? The goal is making this sequence usable for this restricted user without giving him access to the main schema where the sequence actually belongs.

wondering if the ANSI SQL standard has a portable way to create a sequence object and get values from it? I can't find a portable way, but search engines seem to confuse ANSI sql with MS SQL server, so maybe I'm just missing it...

I need to add a new BIGSERIAL column to a huge table (~3 billion records). This question is similar to what I need to do and the accepted answer has helped me somewhat. But I'm still wondering about something. In my case, the table already has a BIGSERIAL column which is the primary key, but many rows have been deleted so now there are *gaps*. (The table has subsequently been fully vacuumed.) I need to regenerate the values so that they are sequential again. Here are 5 example rows of what I want to achieve where the new_value > 1000:

+---------+---------+
|old_value|new_value|
+---------+---------+
|1026     |1001     |
|1027     |1002     |
|1030     |1003     |
|1032     |1004     |
|1039     |1005     |
+---------+---------+

I have successfully implemented the alternative approach as mentioned in the referenced answer above (CREATE TABLE ... and then INSERT INTO new_table SELECT * FROM ... ), but I would also like to attempt, and benchmark against, the initial suggestion. The problem, however, is that I don't know whether the new_value will be generated **in the same order** as the old_value as this is a requirement. How can I ensure the order of the new_value column follows/tracks the order of the old_value column when the new_value column is added using a statement like this:

ALTER TABLE existing_table ADD COLUMN new_value BIGSERIAL;

## A different approach I also attempted the following (that works quite well on a small table), but it's much slower than the alternative suggestion of the referenced answer on very large tables:

ALTER TABLE existing_table ADD COLUMN new_value BIGINT NOT NULL DEFAULT 0;

UPDATE existing_table AS existing
SET new_value = generated.new_id
FROM (
      SELECT original.old_value
           , row_number() OVER (ORDER BY original.old_value) AS new_id
      FROM existing_table AS original
     ) generated
WHERE existing.old_value = generated.old_value;

When my postgres function is tries to create a new sequence, it returns a permission denied error. It looks like the only way to make it work is to give Create permission on schema using the below statement. GRANT CREATE ON SCHEMA public to "myuser" But, this will allow the user to create any other type of object as well, including tables. How can we control this? I want my user to be able to create a sequence, but not tables. Note: Create is not a valid grant on Sequences.

I've been using pgAdmin to create a database and test it. I'm at a point now where I need to install this db on different servers. I've been using pg_dump to do this and I've been able to muddle my way through. But I'm wondering if there's a way to "reset" all the "current" values in any sequence that I have set up? If you have any other general suggestions on how to create "empty" or "blank" databases, can you let me know?

It looks like select nextval('table_name') actually does the value increment. My goal is to "predict" the nextval value on all tables in the database without actually making any incrementation. This should be a read-only operation. I cannot run the select nextval on a read-only connection since it's actually trying to make a transaction. I would like to be able to query this and monitor the sequences on a read-only replica database. How would you tackle this and meet the goal?

So I have a table that has a column labeled 'sequence', it is not in fact a sequence data type, just a numeric field that translates in the object model to show items in a certain order. In a handful of cases due to database manipulation some records will have a duplicate sequence resulting in an inability to re-order in the UI. In other cases there are gaps in sequencing (not a severe problem). What I am looking to do is re-number these numeric fields starting from 1 but it has to group by the 'co' field and then by the existing sequence field you see below: This is the query I ran to get the results you see: SELECT row_number() OVER (ORDER BY C.co, T.sequence) AS RowNum, C.co, T.sequence FROM TNode T INNER JOIN CInfo C ON C.guidfield = T.contextID WHERE T.nodeTypeID = '0EC43D28-EA1F-4FBE-BA50-06161AFAB382' AND T.parentNodeID ='A276DF06-4569-11D5-8052-00D0B7696EF9' GROUP BY C.co, T.sequence What I am looking to achieve is this. Note how the sequence is renumbered slightly on records where it's not a straight 1, 2, 3... but it has to start from 1 again each time a new 'co' is encountered, and then also order by the existing sequence that is there :

After a machine malfunction and a hurried transfer of data onto another machine, we have somehow ended up with some brand new sequences replacing old sequences as the backing for identity columns (and for some old serials). We can RESET the new sequences with the maximum values in the tables, and also use ALTER SEQUENCE ... RENAME to rename the new sequences to the old names (to get around any hard-coding in the client code) but I was wondering if it was possible to swap the old sequence back in place instead?

This evening a developer asked to restore only schemas from a database into another database. A colleague of mine did this, but after that the developer came to me saying that the sequences are missing from the restored schema. I think this was the command my colleague used to get a dump of the schema only : /usr/local/pgsql915/bin/pg_dump -U backupuser --schema-only myegrtx -p 5432 -t tb_roadtax_kioskinfo -t tb_rtx_expdelivery_postcode -t tb_roadtax_inquiryinfo -t roadtax_tx_history -t tb_roadtax_payment_data_info -t tb_roadtax_renewalinfo -t tb_roadtax_kioskusers -t tb_roadtax_kioskuser_trans -t tb_roadtax_delivaryinfo -t tb_roadtax_asinfo -t tb_roadtax_disk_print_info > myegrtxbike-schema.sql And this was the command to restore it to the new db : /usr/local/pgsql934/bin/psql -U postgres -d myegrtxbike -p 6434 -f srikanth/myegrtxbike-schema.sql How do I now get the backup of and restore the sequences that have been reported missing? I need some guide on this, as googling the commands is not a success (The commands do not work or I do not get the proper commands, especially for the sequence part).

I was wondering how to use db2look to get the ddls of sequence. db2look -d my_db_name -z my_proc_schema - I tried this but it is not showing any results. Is there an option to directly mention the sequence name ?

I started writing this post as a question but, while reproducing the error, I found the solution in the PostgreSQL documentation. So I'll share the solution here for the community. A PostgreSQL 14 table had some data deleted accidentally.

sql
    -- Creating table
    CREATE TABLE my_table (
        id            integer      not null  primary key  generated always as identity,
        description   varchar(50)  not null
    );

    -- Filling data
    INSERT INTO my_table (description)
    VALUES
    ('Option A'), ('Option B'), ('Option C'), ('Option D'), ('Option E'),
    ('Option F'), ('Option G'), ('Option H'), ('Option I'), ('Option J');

    SELECT * FROM my_table;

text
id  | description
----+------------
1   | Option A
2   | Option B
3   | Option C
4   | Option D
5   | Option E
6   | Option F
7   | Option G
8   | Option H
9   | Option I
10  | Option J

Accidental deletion:

sql
    DELETE FROM my_table WHERE id IN (6,7,8);

    SELECT * FROM my_table;

text
id  | description
----+------------
1   | Option A
2   | Option B
3   | Option C
4   | Option D
5   | Option E
9   | Option I
10  | Option J

In order to reinsert those rows back into the table, I tried to run the following instruction, explicitly defining values for the "id" column, but it raised an exception:

sql
    INSERT INTO my_table (id, description)
    VALUES
    (6, 'Option F'), (7, 'Option G'), (8, 'Option H');

text
[428C9] ERROR: cannot insert a non-DEFAULT value into column "id" Detail: Column "id" is an identity column defined as GENERATED ALWAYS. Hint: Use OVERRIDING SYSTEM VALUE to override.

When using PHP’s lastInsertId() method with PostgreSQL, it’s necessary to know the name of the sequence. If I defined the column using GENERATED BY DEFAULT AS IDENTITY, I know I can get the sequence name using something like pg_get_serial_sequence('whatever', 'id'). Is it possible to specify a sequence name in the CREATE TABLE statement?