Home > mailing lists

Thread: unexpected results when attempting to define and use column with data type SERIAL

unexpected results when attempting to define and use column with data type SERIAL

From

Michael Rosinger

Date:

15 September 2022, 17:43:26

Version of PostgreSQL: "PostgreSQL 14.4, compiled by Visual C++ build 1914, 64-bit"

Version of PgAdmin: Version

6.13

Application Mode

Desktop

Current User

pgadmin4@pgadmin.org

NW.js Version

0.62.2

Browser

Chromium 99.0.4844.84

Operating System

Windows-10-10.0.19042-SP0

pgAdmin Database File

C:\Users\Michael\AppData\Roaming\pgadmin\pgadmin4.db

Log File

C:\Users\Michael\AppData\Roaming\pgadmin\pgadmin4.log

Server Configuration

ALLOW_SAVE_PASSWORD = True

ALLOW_SAVE_TUNNEL_PASSWORD = False

APP_ICON = "pg-icon"

APP_NAME = "pgAdmin 4"

APP_RELEASE = 6

APP_REVISION = 13

APP_SUFFIX = ""

APP_VERSION = "6.13"

APP_VERSION_EXTN = ('.css', '.js', '.html', '.svg', '.png', '.gif', '.ico')

APP_VERSION_INT = 61300

APP_VERSION_PARAM = "ver"

AUTHENTICATION_SOURCES = ['internal']

AUTO_DISCOVER_SERVERS = True

AZURE_CREDENTIAL_CACHE_DIR = "C:\Users\Michael\AppData\Roaming\pgadmin\azurecredentialcache"

CA_FILE = "C:\Program Files\pgAdmin 4\v6\web\cacert.pem"

CHECK_EMAIL_DELIVERABILITY = False

CHECK_SESSION_FILES_INTERVAL = 24

CHECK_SUPPORTED_BROWSER = True

COMPRESS_LEVEL = 9

COMPRESS_MIMETYPES = ['text/html', 'text/css', 'text/xml', 'application/json', 'application/javascript']

COMPRESS_MIN_SIZE = 500

CONSOLE_LOG_FORMAT = "%(asctime)s: %(levelname)s %(name)s: %(message)s"

CONSOLE_LOG_LEVEL = 30

CONTENT_SECURITY_POLICY = "default-src ws: http: data: blob: 'unsafe-inline' 'unsafe-eval';"

COOKIE_DEFAULT_DOMAIN = None

COOKIE_DEFAULT_PATH = "/"

DATA_DIR = "C:\Users\Michael\AppData\Roaming\pgadmin"

DEBUG = False

DEFAULT_BINARY_PATHS = {'pg': '$DIR/../runtime', 'ppas': ''}

DEFAULT_SERVER = "127.0.0.1"

DEFAULT_SERVER_PORT = 5050

DESKTOP_USER = pgadmin4@pgadmin.org

EFFECTIVE_SERVER_PORT = 58456

ENABLE_BINARY_PATH_BROWSING = False

ENABLE_PSQL = True

ENHANCED_COOKIE_PROTECTION = True

FILE_LOG_FORMAT = "%(asctime)s: %(levelname)s %(name)s: %(message)s"

FILE_LOG_LEVEL = 30

HELP_PATH = "../../../docs/en_US/html/"

IS_WIN = True

KERBEROS_CCACHE_DIR = "C:\Users\Michael\AppData\Roaming\pgadmin\krbccache"

KRB_APP_HOST_NAME = "127.0.0.1"

KRB_AUTO_CREATE_USER = True

KRB_KTNAME = "<KRB5_KEYTAB_FILE>"

LANGUAGES = {'en': 'English', 'br': 'Brazilian', 'zh': 'Chinese (Simplified)', 'cs': 'Czech', 'fr': 'French', 'de': 'German', 'it': 'Italian', 'ja': 'Japanese', 'ko': 'Korean', 'pl': 'Polish', 'ru': 'Russian', 'es': 'Spanish'}

LDAP_ANONYMOUS_BIND = False

LDAP_AUTO_CREATE_USER = True

LDAP_BASE_DN = "<Base-DN>"

LDAP_BIND_USER = None

LDAP_CA_CERT_FILE = ""

LDAP_CERT_FILE = ""

LDAP_CONNECTION_TIMEOUT = 10

LDAP_DN_CASE_SENSITIVE = False

LDAP_KEY_FILE = ""

LDAP_SEARCH_BASE_DN = "<Search-Base-DN>"

LDAP_SEARCH_FILTER = "(objectclass=*)"

LDAP_SEARCH_SCOPE = "SUBTREE"

LDAP_SERVER_URI = "ldap://<ip-address>:<port>"

LDAP_USERNAME_ATTRIBUTE = "<User-id>"

LDAP_USE_STARTTLS = False

LOGIN_ATTEMPT_FIELDS = ['password']

LOGIN_BANNER = ""

LOG_FILE = "C:\Users\Michael\AppData\Roaming\pgadmin\pgadmin4.log"

LOG_ROTATION_AGE = 1440

LOG_ROTATION_MAX_LOG_FILES = 90

LOG_ROTATION_SIZE = 10

MAIL_DEBUG = False

MAIL_PORT = 25

MAIL_SERVER = "localhost"

MAIL_USERNAME = ""

MAIL_USE_SSL = False

MAIL_USE_TLS = False

MASTER_PASSWORD_REQUIRED = True

MAX_LOGIN_ATTEMPTS = 3

MAX_QUERY_HIST_STORED = 20

MAX_SESSION_IDLE_TIME = 60

MFA_EMAIL_SUBJECT = None

MFA_ENABLED = False

MFA_FORCE_REGISTRATION = False

MFA_SUPPORTED_METHODS = ['email', 'authenticator']

MODULE_BLACKLIST = ['test']

NODE_BLACKLIST = []

OAUTH2_AUTO_CREATE_USER = True

OAUTH2_CONFIG = [{'OAUTH2_NAME': None, 'OAUTH2_DISPLAY_NAME': '<Oauth2 Display Name>', 'OAUTH2_CLIENT_ID': None, 'OAUTH2_CLIENT_SECRET': None, 'OAUTH2_TOKEN_URL': None, 'OAUTH2_AUTHORIZATION_URL': None, 'OAUTH2_API_BASE_URL': None, 'OAUTH2_USERINFO_ENDPOINT': None, 'OAUTH2_SCOPE': None, 'OAUTH2_ICON': None, 'OAUTH2_BUTTON_COLOR': None}]

ON_DEMAND_RECORD_COUNT = 1000

OVERRIDE_USER_INACTIVITY_TIMEOUT = True

PG_DEFAULT_DRIVER = "psycopg2"

PROXY_X_FOR_COUNT = 1

PROXY_X_HOST_COUNT = 0

PROXY_X_PORT_COUNT = 1

PROXY_X_PREFIX_COUNT = 0

PROXY_X_PROTO_COUNT = 1

SECURITY_EMAIL_SENDER = "no-reply@localhost"

SECURITY_EMAIL_SUBJECT_PASSWORD_CHANGE_NOTICE = "Your password for pgAdmin 4 has been changed"

SECURITY_EMAIL_SUBJECT_PASSWORD_NOTICE = "Your pgAdmin 4 password has been reset"

SECURITY_EMAIL_SUBJECT_PASSWORD_RESET = "Password reset instructions for pgAdmin 4"

SECURITY_EMAIL_VALIDATOR_ARGS = {'check_deliverability': False}

SEND_FILE_MAX_AGE_DEFAULT = 31556952

SERVER_MODE = False

SESSION_COOKIE_DOMAIN = None

SESSION_COOKIE_HTTPONLY = True

SESSION_COOKIE_NAME = "pga4_session"

SESSION_COOKIE_SAMESITE = "Lax"

SESSION_COOKIE_SECURE = False

SESSION_DB_PATH = "C:\Users\Michael\AppData\Roaming\pgadmin\sessions"

SESSION_EXPIRATION_TIME = 7

SESSION_SKIP_PATHS = ['/misc/ping']

SETTINGS_SCHEMA_VERSION = 33

SHOW_GRAVATAR_IMAGE = True

SQLALCHEMY_TRACK_MODIFICATIONS = False

SQLITE_PATH = "C:\Users\Michael\AppData\Roaming\pgadmin\pgadmin4.db"

SQLITE_TIMEOUT = 500

STORAGE_DIR = "C:\Users\Michael\AppData\Roaming\pgadmin\storage"

STRICT_TRANSPORT_SECURITY = "max-age=31536000; includeSubDomains"

STRICT_TRANSPORT_SECURITY_ENABLED = False

SUPPORT_SSH_TUNNEL = True

TEST_SQLITE_PATH = "C:\Users\Michael\AppData\Roaming\pgadmin\test_pgadmin4.db"

THREADED_MODE = True

UPGRADE_CHECK_ENABLED = True

UPGRADE_CHECK_KEY = "pgadmin4"

UPGRADE_CHECK_URL = https://www.pgadmin.org/versions.json

USER_INACTIVITY_TIMEOUT = 0

WEBSERVER_AUTO_CREATE_USER = True

WEBSERVER_REMOTE_USER = "REMOTE_USER"

WEB_SERVER = "Python"

WTF_CSRF_HEADERS = ['X-pgA-CSRFToken']

X_CONTENT_TYPE_OPTIONS = "nosniff"

X_FRAME_OPTIONS = "SAMEORIGIN"

X_XSS_PROTECTION = "1; mode=block"

Description of issue: I have had some problems with the data type SERIAL while working in the environment described above. There are several different issues but all related to data type SERIAL.
1. When attempting to change a PK column from INTEGER to SERIAL in an existing (populated) table using the table Properties dialog in PgAdmin, the choice of SERIAL could not be entered and the option did not appear in the dropdown list of data types.
2. When attempting to modify the same column’s data type using an SQL script with ALTER TABLE ALTER COLUMN, the query failed as it did not recognize the data type “serial”.
3. When rebuilding the same table using CREATE TABLE and specifying the PK column as SERIAL, the CREATE statement worked, but afterwards when reviewing the table properties in PgAdmin, the data type showed as INTEGER with a DEFAULT of “nextval('suppliers_supplier_id_seq'::regclass)”.
4. According to the documentation (https://www.postgresql.org/docs/14/datatype-numeric.html#DATATYPE-SERIAL), the range for SERIAL is defined as “1 to 2147483647”, yet when adding rows to the table and specifying the value for the PK (SERIAL) column as either 0 (zero) or any negative number, the INSERT was accepted and the defined range limitation was NOT enforced. In this instance I want the DB to enforce that the value must be a positive INTEGER, but it failed to do that.
5. On the positive side, I can confirm that adding rows to the table without specifying a value for the PK SERIAL column DOES work correctly, and the DB generates the next available value.

I do not believe this is correct behavior for a SERIAL column, but I may be mistaken. I have searched for articles on the symptoms I have described but so far have not found anything that even resembles it.

If this is a BUG, please resolve it in the next release.

If not, please explain what I am not understanding (and add documentation to this effect to the section on SERIAL).

Thanks!

Regards,

Michael Rosinger

Product Development, MegaVoice

Re: unexpected results when attempting to define and use column with data type SERIAL

From

Tom Lane

Date:

15 September 2022, 18:49:15

Michael Rosinger <mrosinger@megavoice.com> writes:
>   1.  Description of issue:               I have had some problems with the data type SERIAL while working in the
environmentdescribed above. There are several different issues but all related to data type SERIAL. 
>      *   When attempting to change a PK column from INTEGER to SERIAL in an existing (populated) table using the
tableProperties dialog in PgAdmin, the choice of SERIAL could not be entered and the option did not appear in the
dropdownlist of data types. 
>      *   When attempting to modify the same column's data type using an SQL script with ALTER TABLE ALTER COLUMN, the
queryfailed as it did not recognize the data type "serial". 

That would be because SERIAL is indeed not a type.  It's a shorthand that
CREATE TABLE recognizes for creating an integer column with a specific
default expression, as explained at

https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIAL

We have not made ALTER TABLE, or any other commands, understand
SERIAL in the same way.  It's a bit of a legacy feature, now
that the SQL standard has defined other syntax for the same task.

>      *   When rebuilding the same table using CREATE TABLE and specifying the PK column as SERIAL, the CREATE
statementworked, but afterwards when reviewing the table properties in PgAdmin, the data type showed as INTEGER with a
DEFAULTof "nextval('suppliers_supplier_id_seq'::regclass)". 

That's exactly what it's supposed to do.

>      *   According to the documentation (https://www.postgresql.org/docs/14/datatype-numeric.html#DATATYPE-SERIAL),
therange for SERIAL is defined as "1 to 2147483647", yet when adding rows to the table and specifying the value for the
PK(SERIAL) column as either 0 (zero) or any negative number, the INSERT was accepted and the defined range limitation
wasNOT enforced. In this instance I want the DB to enforce that the value must be a positive INTEGER, but it failed to
dothat. 

What that's referring to is the default properties of the attached
sequence generator.  The underlying column is just integer, so it
will willingly store any integer.  If you want an additional
range constraint, you could attach a CHECK constraint to the column.

>   1.  I do not believe this is correct behavior for a SERIAL column, but I may be mistaken. I have searched for
articleson the symptoms I have described but so far have not found anything that even resembles it. 

You seem to not have read the documentation section you cite, or
else it's so poorly written that you didn't absorb what it was
trying to say.  Can you suggest a rewording that would make it
clearer?

            regards, tom lane