Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Surfacing system columns xmin, xmax, etc in views? #36

Open
aschleck opened this issue Feb 9, 2025 · 0 comments
Open

Surfacing system columns xmin, xmax, etc in views? #36

aschleck opened this issue Feb 9, 2025 · 0 comments

Comments

@aschleck
Copy link
Contributor

aschleck commented Feb 9, 2025

In postgres every table has a number of additional columns: https://www.postgresql.org/docs/9.1/ddl-system-columns.html

They are useful in some situations, for example I use xmax to detect conflicts when inserting objects with randomly generated, human readable IDs.

        INSERT INTO objects (
          id,
          created,
          location
        ) VALUES ($1, CURRENT_TIMESTAMP, $2)
        ON CONFLICT (id) DO NOTHING
        RETURNING (xmax = 0) AS conflict_free

Then I can check conflict_free in my application logic and retry with a new ID if it's set. I suppose the other way to do this is to drop the ON CONFLICT DO NOTHING but now I have to catch the exception, verify it failed due to the conflict and not some other problem, and retry etc. Overall I think it's better to do this than to handle control flow via exceptions.

Anyway, with reshape views, none of these columns are available. Would it be reasonable to surface any of these system columns? They don't seem problematic to me but I'm not very familiar with how reshape works under the hood.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant