# File lib/sequel/adapters/shared/postgres.rb 701 def primary_key(table, opts=OPTS) 702 quoted_table = quote_schema_table(table) 703 Sequel.synchronize{return @primary_keys[quoted_table] if @primary_keys.has_key?(quoted_table)} 704 value = _select_pk_ds.where_single_value(Sequel[:pg_class][:oid] => regclass_oid(table, opts)) 705 Sequel.synchronize{@primary_keys[quoted_table] = value} 706 end
module Sequel::Postgres::DatabaseMethods
Constants
- DATABASE_ERROR_REGEXPS
- FOREIGN_KEY_LIST_ON_DELETE_MAP
- MAX_DATE
- MAX_TIMESTAMP
- MIN_DATE
- MIN_TIMESTAMP
- ON_COMMIT
- SELECT_CUSTOM_SEQUENCE_SQL
SQL
fragment for custom sequences (ones not created by serial primary key), Returning the schema and literal form of the sequence name, by parsing the column defaults table.- SELECT_PK_SQL
SQL
fragment for determining primary key column for the given table. Only returns the first primary key if the table has a composite primary key.- SELECT_SERIAL_SEQUENCE_SQL
SQL
fragment for getting sequence associated with table’s primary key, assuming it was a serial primary key column.- TYPTYPE_METHOD_MAP
- VALID_CLIENT_MIN_MESSAGES
Attributes
A hash of conversion procs, keyed by type integer (oid) and having callable values for the conversion proc for that type.
Public Instance Methods
Set a conversion proc for the given oid. The callable can be passed either as a argument or a block.
# File lib/sequel/adapters/shared/postgres.rb 320 def add_conversion_proc(oid, callable=nil, &block) 321 conversion_procs[oid] = callable || block 322 end
Add a conversion proc for a named type, using the given block. This should be used for types without fixed OIDs, which includes all types that are not included in a default PostgreSQL installation.
# File lib/sequel/adapters/shared/postgres.rb 327 def add_named_conversion_proc(name, &block) 328 unless oid = from(:pg_type).where(:typtype=>['b', 'e'], :typname=>name.to_s).get(:oid) 329 raise Error, "No matching type in pg_type for #{name.inspect}" 330 end 331 add_conversion_proc(oid, block) 332 end
A hash of metadata for CHECK constraints on the table. Keys are CHECK constraint name symbols. Values are hashes with the following keys:
- :definition
-
An
SQL
fragment for the definition of the constraint - :columns
-
An array of column symbols for the columns referenced in the constraint, can be an empty array if the database cannot deteremine the column symbols.
# File lib/sequel/adapters/shared/postgres.rb 343 def check_constraints(table) 344 m = output_identifier_meth 345 346 hash = {} 347 _check_constraints_ds.where_each(:conrelid=>regclass_oid(table)) do |row| 348 constraint = m.call(row[:constraint]) 349 entry = hash[constraint] ||= {:definition=>row[:definition], :columns=>[], :validated=>row[:validated], :enforced=>row[:enforced]} 350 entry[:columns] << m.call(row[:column]) if row[:column] 351 end 352 353 hash 354 end
# File lib/sequel/adapters/shared/postgres.rb 334 def commit_prepared_transaction(transaction_id, opts=OPTS) 335 run("COMMIT PREPARED #{literal(transaction_id)}", opts) 336 end
Convert the first primary key column in the table
from being a serial column to being an identity column. If the column is already an identity column, assume it was already converted and make no changes.
Only supported on PostgreSQL 10.2+, since on those versions Sequel
will use identity columns instead of serial columns for auto incrementing primary keys. Only supported when running as a superuser, since regular users cannot modify system tables, and there is no way to keep an existing sequence when changing an existing column to be an identity column.
This method can raise an exception in at least the following cases where it may otherwise succeed (there may be additional cases not listed here):
-
The serial column was added after table creation using PostgreSQL <7.3
-
A regular index also exists on the column (such an index can probably be dropped as the primary key index should suffice)
Options:
- :column
-
Specify the column to convert instead of using the first primary key column
- :server
-
Run the
SQL
on the given server
# File lib/sequel/adapters/shared/postgres.rb 374 def convert_serial_to_identity(table, opts=OPTS) 375 raise Error, "convert_serial_to_identity is only supported on PostgreSQL 10.2+" unless server_version >= 100002 376 377 server = opts[:server] 378 server_hash = server ? {:server=>server} : OPTS 379 ds = dataset 380 ds = ds.server(server) if server 381 382 raise Error, "convert_serial_to_identity requires superuser permissions" unless ds.get{current_setting('is_superuser')} == 'on' 383 384 table_oid = regclass_oid(table) 385 im = input_identifier_meth 386 unless column = (opts[:column] || ((sch = schema(table).find{|_, sc| sc[:primary_key] && sc[:auto_increment]}) && sch[0])) 387 raise Error, "could not determine column to convert from serial to identity automatically" 388 end 389 column = im.call(column) 390 391 column_num = ds.from(:pg_attribute). 392 where(:attrelid=>table_oid, :attname=>column). 393 get(:attnum) 394 395 pg_class = Sequel.cast('pg_class', :regclass) 396 res = ds.from(:pg_depend). 397 where(:refclassid=>pg_class, :refobjid=>table_oid, :refobjsubid=>column_num, :classid=>pg_class, :objsubid=>0, :deptype=>%w'a i'). 398 select_map([:objid, Sequel.as({:deptype=>'i'}, :v)]) 399 400 case res.length 401 when 0 402 raise Error, "unable to find related sequence when converting serial to identity" 403 when 1 404 seq_oid, already_identity = res.first 405 else 406 raise Error, "more than one linked sequence found when converting serial to identity" 407 end 408 409 return if already_identity 410 411 transaction(server_hash) do 412 run("ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(column)} DROP DEFAULT", server_hash) 413 414 ds.from(:pg_depend). 415 where(:classid=>pg_class, :objid=>seq_oid, :objsubid=>0, :deptype=>'a'). 416 update(:deptype=>'i') 417 418 ds.from(:pg_attribute). 419 where(:attrelid=>table_oid, :attname=>column). 420 update(:attidentity=>'d') 421 end 422 423 remove_cached_schema(table) 424 nil 425 end
Creates the function in the database. Arguments:
- name
-
name of the function to create
- definition
-
string definition of the function, or object file for a dynamically loaded C function.
- opts
-
options hash:
- :args
-
function arguments, can be either a symbol or string specifying a type or an array of 1-3 elements:
- 1
-
argument data type
- 2
-
argument name
- 3
-
argument mode (e.g. in, out, inout)
- :behavior
-
Should be IMMUTABLE, STABLE, or VOLATILE. PostgreSQL assumes VOLATILE by default.
- :parallel
-
The thread safety attribute of the function. Should be SAFE, UNSAFE, RESTRICTED. PostgreSQL assumes UNSAFE by default.
- :cost
-
The estimated cost of the function, used by the query planner.
- :language
-
The language the function uses.
SQL
is the default. - :link_symbol
-
For a dynamically loaded see function, the function’s link symbol if different from the definition argument.
- :returns
-
The data type returned by the function. If you are using OUT or INOUT argument modes, this is ignored. Otherwise, if this is not specified, void is used by default to specify the function is not supposed to return a value.
- :rows
-
The estimated number of rows the function will return. Only use if the function returns SETOF something.
- :security_definer
-
Makes the privileges of the function the same as the privileges of the user who defined the function instead of the privileges of the user who runs the function. There are security implications when doing this, see the PostgreSQL documentation.
- :set
-
Configuration variables to set while the function is being run, can be a hash or an array of two pairs. search_path is often used here if :security_definer is used.
- :strict
-
Makes the function return NULL when any argument is NULL.
# File lib/sequel/adapters/shared/postgres.rb 448 def create_function(name, definition, opts=OPTS) 449 self << create_function_sql(name, definition, opts) 450 end
Create the procedural language in the database. Arguments:
- name
-
Name of the procedural language (e.g. plpgsql)
- opts
-
options hash:
- :handler
-
The name of a previously registered function used as a call handler for this language.
- :replace
-
Replace the installed language if it already exists (on PostgreSQL 9.0+).
- :trusted
-
Marks the language being created as trusted, allowing unprivileged users to create functions using this language.
- :validator
-
The name of previously registered function used as a validator of functions defined in this language.
# File lib/sequel/adapters/shared/postgres.rb 459 def create_language(name, opts=OPTS) 460 self << create_language_sql(name, opts) 461 end
Create a schema in the database. Arguments:
- name
-
Name of the schema (e.g. admin)
- opts
-
options hash:
- :if_not_exists
-
Don’t raise an error if the schema already exists (PostgreSQL 9.3+)
- :owner
-
The owner to set for the schema (defaults to current user if not specified)
# File lib/sequel/adapters/shared/postgres.rb 468 def create_schema(name, opts=OPTS) 469 self << create_schema_sql(name, opts) 470 end
Support partitions of tables using the :partition_of option.
# File lib/sequel/adapters/shared/postgres.rb 473 def create_table(name, options=OPTS, &block) 474 if options[:partition_of] 475 create_partition_of_table_from_generator(name, CreatePartitionOfTableGenerator.new(&block), options) 476 return 477 end 478 479 super 480 end
Support partitions of tables using the :partition_of option.
# File lib/sequel/adapters/shared/postgres.rb 483 def create_table?(name, options=OPTS, &block) 484 if options[:partition_of] 485 create_table(name, options.merge!(:if_not_exists=>true), &block) 486 return 487 end 488 489 super 490 end
Create a trigger in the database. Arguments:
- table
-
the table on which this trigger operates
- name
-
the name of this trigger
- function
-
the function to call for this trigger, which should return type trigger.
- opts
-
options hash:
- :after
-
Calls the trigger after execution instead of before.
- :args
-
An argument or array of arguments to pass to the function.
- :each_row
-
Calls the trigger for each row instead of for each statement.
- :events
-
Can be :insert, :update, :delete, or an array of any of those. Calls the trigger whenever that type of statement is used. By default, the trigger is called for insert, update, or delete.
- :replace
-
Replace the trigger with the same name if it already exists (PostgreSQL 14+).
- :when
-
A filter to use for the trigger
# File lib/sequel/adapters/shared/postgres.rb 504 def create_trigger(table, name, function, opts=OPTS) 505 self << create_trigger_sql(table, name, function, opts) 506 end
# File lib/sequel/adapters/shared/postgres.rb 508 def database_type 509 :postgres 510 end
For constraints that are deferrable, defer constraints until transaction commit. Options:
- :constraints
-
An identifier of the constraint, or an array of identifiers for constraints, to apply this change to specific constraints.
- :server
-
The server/shard on which to run the query.
Examples:
DB.defer_constraints # SET CONSTRAINTS ALL DEFERRED DB.defer_constraints(constraints: [:c1, Sequel[:sc][:c2]]) # SET CONSTRAINTS "c1", "sc"."s2" DEFERRED
# File lib/sequel/adapters/shared/postgres.rb 527 def defer_constraints(opts=OPTS) 528 _set_constraints(' DEFERRED', opts) 529 end
Use PostgreSQL’s DO syntax to execute an anonymous code block. The code should be the literal code string to use in the underlying procedural language. Options:
- :language
-
The procedural language the code is written in. The PostgreSQL default is plpgsql. Can be specified as a string or a symbol.
# File lib/sequel/adapters/shared/postgres.rb 536 def do(code, opts=OPTS) 537 language = opts[:language] 538 run "DO #{"LANGUAGE #{literal(language.to_s)} " if language}#{literal(code)}" 539 end
Drops the function from the database. Arguments:
- name
-
name of the function to drop
- opts
-
options hash:
- :args
-
The arguments for the function. See create_function_sql.
- :cascade
-
Drop other objects depending on this function.
- :if_exists
-
Don’t raise an error if the function doesn’t exist.
# File lib/sequel/adapters/shared/postgres.rb 547 def drop_function(name, opts=OPTS) 548 self << drop_function_sql(name, opts) 549 end
Drops a procedural language from the database. Arguments:
- name
-
name of the procedural language to drop
- opts
-
options hash:
- :cascade
-
Drop other objects depending on this function.
- :if_exists
-
Don’t raise an error if the function doesn’t exist.
# File lib/sequel/adapters/shared/postgres.rb 556 def drop_language(name, opts=OPTS) 557 self << drop_language_sql(name, opts) 558 end
Drops a schema from the database. Arguments:
- name
-
name of the schema to drop
- opts
-
options hash:
- :cascade
-
Drop all objects in this schema.
- :if_exists
-
Don’t raise an error if the schema doesn’t exist.
# File lib/sequel/adapters/shared/postgres.rb 565 def drop_schema(name, opts=OPTS) 566 self << drop_schema_sql(name, opts) 567 end
Drops a trigger from the database. Arguments:
- table
-
table from which to drop the trigger
- name
-
name of the trigger to drop
- opts
-
options hash:
- :cascade
-
Drop other objects depending on this function.
- :if_exists
-
Don’t raise an error if the function doesn’t exist.
# File lib/sequel/adapters/shared/postgres.rb 575 def drop_trigger(table, name, opts=OPTS) 576 self << drop_trigger_sql(table, name, opts) 577 end
Return full foreign key information using the pg system tables, including :name, :on_delete, :on_update, and :deferrable entries in the hashes.
Supports additional options:
- :reverse
-
Instead of returning foreign keys in the current table, return foreign keys in other tables that reference the current table.
- :schema
-
Set to true to have the :table value in the hashes be a qualified identifier. Set to false to use a separate :schema value with the related schema. Defaults to whether the given table argument is a qualified identifier.
# File lib/sequel/adapters/shared/postgres.rb 589 def foreign_key_list(table, opts=OPTS) 590 m = output_identifier_meth 591 schema, _ = opts.fetch(:schema, schema_and_table(table)) 592 593 h = {} 594 fklod_map = FOREIGN_KEY_LIST_ON_DELETE_MAP 595 reverse = opts[:reverse] 596 597 (reverse ? _reverse_foreign_key_list_ds : _foreign_key_list_ds).where_each(Sequel[:cl][:oid]=>regclass_oid(table)) do |row| 598 if reverse 599 key = [row[:schema], row[:table], row[:name]] 600 else 601 key = row[:name] 602 end 603 604 if r = h[key] 605 r[:columns] << m.call(row[:column]) 606 r[:key] << m.call(row[:refcolumn]) 607 else 608 entry = h[key] = { 609 :name=>m.call(row[:name]), 610 :columns=>[m.call(row[:column])], 611 :key=>[m.call(row[:refcolumn])], 612 :on_update=>fklod_map[row[:on_update]], 613 :on_delete=>fklod_map[row[:on_delete]], 614 :deferrable=>row[:deferrable], 615 :validated=>row[:validated], 616 :enforced=>row[:enforced], 617 :table=>schema ? SQL::QualifiedIdentifier.new(m.call(row[:schema]), m.call(row[:table])) : m.call(row[:table]), 618 } 619 620 unless schema 621 # If not combining schema information into the :table entry 622 # include it as a separate entry. 623 entry[:schema] = m.call(row[:schema]) 624 end 625 end 626 end 627 628 h.values 629 end
# File lib/sequel/adapters/shared/postgres.rb 631 def freeze 632 server_version 633 supports_prepared_transactions? 634 _schema_ds 635 _select_serial_sequence_ds 636 _select_custom_sequence_ds 637 _select_pk_ds 638 _indexes_ds 639 _check_constraints_ds 640 _foreign_key_list_ds 641 _reverse_foreign_key_list_ds 642 @conversion_procs.freeze 643 super 644 end
Immediately apply deferrable constraints.
- :constraints
-
An identifier of the constraint, or an array of identifiers for constraints, to apply this change to specific constraints.
- :server
-
The server/shard on which to run the query.
Examples:
DB.immediate_constraints # SET CONSTRAINTS ALL IMMEDIATE DB.immediate_constraints(constraints: [:c1, Sequel[:sc][:c2]]) # SET CONSTRAINTS "c1", "sc"."s2" IMMEDIATE
# File lib/sequel/adapters/shared/postgres.rb 660 def immediate_constraints(opts=OPTS) 661 _set_constraints(' IMMEDIATE', opts) 662 end
Use the pg_* system tables to determine indexes on a table
# File lib/sequel/adapters/shared/postgres.rb 665 def indexes(table, opts=OPTS) 666 m = output_identifier_meth 667 cond = {Sequel[:tab][:oid]=>regclass_oid(table, opts)} 668 cond[:indpred] = nil unless opts[:include_partial] 669 670 indexes = {} 671 _indexes_ds.where_each(cond) do |r| 672 i = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>r[:unique], :deferrable=>r[:deferrable]} 673 i[:columns] << m.call(r[:column]) 674 end 675 indexes 676 end
Dataset
containing all current database locks
# File lib/sequel/adapters/shared/postgres.rb 679 def locks 680 dataset.from(:pg_class).join(:pg_locks, :relation=>:relfilenode).select{[pg_class[:relname], Sequel::SQL::ColumnAll.new(:pg_locks)]} 681 end
Notifies the given channel. See the PostgreSQL NOTIFY documentation. Options:
- :payload
-
The payload string to use for the NOTIFY statement. Only supported in PostgreSQL 9.0+.
- :server
-
The server to which to send the NOTIFY statement, if the sharding support is being used.
# File lib/sequel/adapters/shared/postgres.rb 689 def notify(channel, opts=OPTS) 690 sql = String.new 691 sql << "NOTIFY " 692 dataset.send(:identifier_append, sql, channel) 693 if payload = opts[:payload] 694 sql << ", " 695 dataset.literal_append(sql, payload.to_s) 696 end 697 execute_ddl(sql, opts) 698 end
Return primary key for the given table.
Return the sequence providing the default for the primary key for the given table.
# File lib/sequel/adapters/shared/postgres.rb 709 def primary_key_sequence(table, opts=OPTS) 710 quoted_table = quote_schema_table(table) 711 Sequel.synchronize{return @primary_key_sequences[quoted_table] if @primary_key_sequences.has_key?(quoted_table)} 712 cond = {Sequel[:t][:oid] => regclass_oid(table, opts)} 713 value = if pks = _select_serial_sequence_ds.first(cond) 714 literal(SQL::QualifiedIdentifier.new(pks[:schema], pks[:sequence])) 715 elsif pks = _select_custom_sequence_ds.first(cond) 716 literal(SQL::QualifiedIdentifier.new(pks[:schema], LiteralString.new(pks[:sequence]))) 717 end 718 719 Sequel.synchronize{@primary_key_sequences[quoted_table] = value} if value 720 end
Refresh the materialized view with the given name.
DB.refresh_view(:items_view) # REFRESH MATERIALIZED VIEW items_view DB.refresh_view(:items_view, concurrently: true) # REFRESH MATERIALIZED VIEW CONCURRENTLY items_view
# File lib/sequel/adapters/shared/postgres.rb 728 def refresh_view(name, opts=OPTS) 729 run "REFRESH MATERIALIZED VIEW#{' CONCURRENTLY' if opts[:concurrently]} #{quote_schema_table(name)}" 730 end
Reset the primary key sequence for the given table, basing it on the maximum current value of the table’s primary key.
# File lib/sequel/adapters/shared/postgres.rb 734 def reset_primary_key_sequence(table) 735 return unless seq = primary_key_sequence(table) 736 pk = SQL::Identifier.new(primary_key(table)) 737 db = self 738 s, t = schema_and_table(table) 739 table = Sequel.qualify(s, t) if s 740 741 if server_version >= 100000 742 seq_ds = metadata_dataset.from(:pg_sequence).where(:seqrelid=>regclass_oid(LiteralString.new(seq))) 743 increment_by = :seqincrement 744 min_value = :seqmin 745 # :nocov: 746 else 747 seq_ds = metadata_dataset.from(LiteralString.new(seq)) 748 increment_by = :increment_by 749 min_value = :min_value 750 # :nocov: 751 end 752 753 get{setval(seq, db[table].select(coalesce(max(pk)+seq_ds.select(increment_by), seq_ds.select(min_value))), false)} 754 end
# File lib/sequel/adapters/shared/postgres.rb 756 def rollback_prepared_transaction(transaction_id, opts=OPTS) 757 run("ROLLBACK PREPARED #{literal(transaction_id)}", opts) 758 end
PostgreSQL uses SERIAL psuedo-type instead of AUTOINCREMENT for managing incrementing primary keys.
# File lib/sequel/adapters/shared/postgres.rb 762 def serial_primary_key_options 763 # :nocov: 764 auto_increment_key = server_version >= 100002 ? :identity : :serial 765 # :nocov: 766 {:primary_key => true, auto_increment_key => true, :type=>Integer} 767 end
The version of the PostgreSQL server, used for determining capability.
# File lib/sequel/adapters/shared/postgres.rb 770 def server_version(server=nil) 771 return @server_version if @server_version 772 ds = dataset 773 ds = ds.server(server) if server 774 @server_version = swallow_database_error{ds.with_sql("SELECT CAST(current_setting('server_version_num') AS integer) AS v").single_value} || 0 775 end
PostgreSQL supports CREATE TABLE IF NOT EXISTS on 9.1+
# File lib/sequel/adapters/shared/postgres.rb 778 def supports_create_table_if_not_exists? 779 server_version >= 90100 780 end
PostgreSQL 9.0+ supports some types of deferrable constraints beyond foreign key constraints.
# File lib/sequel/adapters/shared/postgres.rb 783 def supports_deferrable_constraints? 784 server_version >= 90000 785 end
PostgreSQL supports deferrable foreign key constraints.
# File lib/sequel/adapters/shared/postgres.rb 788 def supports_deferrable_foreign_key_constraints? 789 true 790 end
PostgreSQL supports DROP TABLE IF EXISTS
# File lib/sequel/adapters/shared/postgres.rb 793 def supports_drop_table_if_exists? 794 true 795 end
PostgreSQL supports partial indexes.
# File lib/sequel/adapters/shared/postgres.rb 798 def supports_partial_indexes? 799 true 800 end
PostgreSQL supports prepared transactions (two-phase commit) if max_prepared_transactions is greater than 0.
# File lib/sequel/adapters/shared/postgres.rb 809 def supports_prepared_transactions? 810 return @supports_prepared_transactions if defined?(@supports_prepared_transactions) 811 @supports_prepared_transactions = self['SHOW max_prepared_transactions'].get.to_i > 0 812 end
PostgreSQL supports savepoints
# File lib/sequel/adapters/shared/postgres.rb 815 def supports_savepoints? 816 true 817 end
PostgreSQL supports transaction isolation levels
# File lib/sequel/adapters/shared/postgres.rb 820 def supports_transaction_isolation_levels? 821 true 822 end
PostgreSQL supports transaction DDL statements.
# File lib/sequel/adapters/shared/postgres.rb 825 def supports_transactional_ddl? 826 true 827 end
PostgreSQL 9.0+ supports trigger conditions.
# File lib/sequel/adapters/shared/postgres.rb 803 def supports_trigger_conditions? 804 server_version >= 90000 805 end
Array
of symbols specifying table names in the current database. The dataset used is yielded to the block if one is provided, otherwise, an array of symbols of table names is returned.
Options:
- :qualify
-
Return the tables as
Sequel::SQL::QualifiedIdentifier
instances, using the schema the table is located in as the qualifier. - :schema
-
The schema to search
- :server
-
The server to use
# File lib/sequel/adapters/shared/postgres.rb 838 def tables(opts=OPTS, &block) 839 pg_class_relname(['r', 'p'], opts, &block) 840 end
Check whether the given type name string/symbol (e.g. :hstore) is supported by the database.
# File lib/sequel/adapters/shared/postgres.rb 844 def type_supported?(type) 845 Sequel.synchronize{return @supported_types[type] if @supported_types.has_key?(type)} 846 supported = from(:pg_type).where(:typtype=>'b', :typname=>type.to_s).count > 0 847 Sequel.synchronize{return @supported_types[type] = supported} 848 end
Creates a dataset that uses the VALUES clause:
DB.values([[1, 2], [3, 4]]) # VALUES ((1, 2), (3, 4)) DB.values([[1, 2], [3, 4]]).order(:column2).limit(1, 1) # VALUES ((1, 2), (3, 4)) ORDER BY column2 LIMIT 1 OFFSET 1
# File lib/sequel/adapters/shared/postgres.rb 857 def values(v) 858 raise Error, "Cannot provide an empty array for values" if v.empty? 859 @default_dataset.clone(:values=>v) 860 end
Array
of symbols specifying view names in the current database.
Options:
- :materialized
-
Return materialized views
- :qualify
-
Return the views as
Sequel::SQL::QualifiedIdentifier
instances, using the schema the view is located in as the qualifier. - :schema
-
The schema to search
- :server
-
The server to use
# File lib/sequel/adapters/shared/postgres.rb 870 def views(opts=OPTS) 871 relkind = opts[:materialized] ? 'm' : 'v' 872 pg_class_relname(relkind, opts) 873 end
Attempt to acquire an exclusive advisory lock with the given lock_id (which should be a 64-bit integer). If successful, yield to the block, then release the advisory lock when the block exits. If unsuccessful, raise a Sequel::AdvisoryLockError.
DB.with_advisory_lock(1347){DB.get(1)} # SELECT pg_try_advisory_lock(1357) LIMIT 1 # SELECT 1 AS v LIMIT 1 # SELECT pg_advisory_unlock(1357) LIMIT 1
Options:
- :wait
-
Do not raise an error, instead, wait until the advisory lock can be acquired.
# File lib/sequel/adapters/shared/postgres.rb 886 def with_advisory_lock(lock_id, opts=OPTS) 887 ds = dataset 888 if server = opts[:server] 889 ds = ds.server(server) 890 end 891 892 synchronize(server) do |c| 893 begin 894 if opts[:wait] 895 ds.get{pg_advisory_lock(lock_id)} 896 locked = true 897 else 898 unless locked = ds.get{pg_try_advisory_lock(lock_id)} 899 raise AdvisoryLockError, "unable to acquire advisory lock #{lock_id.inspect}" 900 end 901 end 902 903 yield 904 ensure 905 ds.get{pg_advisory_unlock(lock_id)} if locked 906 end 907 end 908 end
Private Instance Methods
Build dataset used for foreign key list methods.
# File lib/sequel/adapters/shared/postgres.rb 936 def __foreign_key_list_ds(reverse) 937 if reverse 938 ctable = Sequel[:att2] 939 cclass = Sequel[:cl2] 940 rtable = Sequel[:att] 941 rclass = Sequel[:cl] 942 else 943 ctable = Sequel[:att] 944 cclass = Sequel[:cl] 945 rtable = Sequel[:att2] 946 rclass = Sequel[:cl2] 947 end 948 949 if server_version >= 90500 950 cpos = Sequel.expr{array_position(co[:conkey], ctable[:attnum])} 951 rpos = Sequel.expr{array_position(co[:confkey], rtable[:attnum])} 952 # :nocov: 953 else 954 range = 0...32 955 cpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:conkey], [x]), x]}, 32, ctable[:attnum])} 956 rpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:confkey], [x]), x]}, 32, rtable[:attnum])} 957 # :nocov: 958 end 959 960 ds = metadata_dataset. 961 from{pg_constraint.as(:co)}. 962 join(Sequel[:pg_class].as(cclass), :oid=>:conrelid). 963 join(Sequel[:pg_attribute].as(ctable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:conkey])). 964 join(Sequel[:pg_class].as(rclass), :oid=>Sequel[:co][:confrelid]). 965 join(Sequel[:pg_attribute].as(rtable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:confkey])). 966 join(Sequel[:pg_namespace].as(:nsp), :oid=>Sequel[:cl2][:relnamespace]). 967 order{[co[:conname], cpos]}. 968 where{{ 969 cl[:relkind]=>%w'r p', 970 co[:contype]=>'f', 971 cpos=>rpos 972 }}. 973 select{[ 974 co[:conname].as(:name), 975 ctable[:attname].as(:column), 976 co[:confupdtype].as(:on_update), 977 co[:confdeltype].as(:on_delete), 978 cl2[:relname].as(:table), 979 rtable[:attname].as(:refcolumn), 980 SQL::BooleanExpression.new(:AND, co[:condeferrable], co[:condeferred]).as(:deferrable), 981 nsp[:nspname].as(:schema) 982 ]} 983 984 if reverse 985 ds = ds.order_append(Sequel[:nsp][:nspname], Sequel[:cl2][:relname]) 986 end 987 988 _add_validated_enforced_constraint_columns(ds) 989 end
# File lib/sequel/adapters/shared/postgres.rb 991 def _add_validated_enforced_constraint_columns(ds) 992 validated_cond = if server_version >= 90100 993 Sequel[:convalidated] 994 # :nocov: 995 else 996 Sequel.cast(true, TrueClass) 997 # :nocov: 998 end 999 ds = ds.select_append(validated_cond.as(:validated)) 1000 1001 enforced_cond = if server_version >= 180000 1002 Sequel[:conenforced] 1003 # :nocov: 1004 else 1005 Sequel.cast(true, TrueClass) 1006 # :nocov: 1007 end 1008 ds = ds.select_append(enforced_cond.as(:enforced)) 1009 1010 ds 1011 end
Dataset
used to retrieve CHECK constraint information
# File lib/sequel/adapters/shared/postgres.rb 913 def _check_constraints_ds 914 @_check_constraints_ds ||= begin 915 ds = metadata_dataset. 916 from{pg_constraint.as(:co)}. 917 left_join(Sequel[:pg_attribute].as(:att), :attrelid=>:conrelid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:conkey])). 918 where(:contype=>'c'). 919 select{[co[:conname].as(:constraint), att[:attname].as(:column), pg_get_constraintdef(co[:oid]).as(:definition)]} 920 921 _add_validated_enforced_constraint_columns(ds) 922 end 923 end
Dataset
used to retrieve foreign keys referenced by a table
# File lib/sequel/adapters/shared/postgres.rb 926 def _foreign_key_list_ds 927 @_foreign_key_list_ds ||= __foreign_key_list_ds(false) 928 end
Dataset
used to retrieve index information
# File lib/sequel/adapters/shared/postgres.rb 1014 def _indexes_ds 1015 @_indexes_ds ||= begin 1016 if server_version >= 90500 1017 order = [Sequel[:indc][:relname], Sequel.function(:array_position, Sequel[:ind][:indkey], Sequel[:att][:attnum])] 1018 # :nocov: 1019 else 1020 range = 0...32 1021 order = [Sequel[:indc][:relname], SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(Sequel[:ind][:indkey], [x]), x]}, 32, Sequel[:att][:attnum])] 1022 # :nocov: 1023 end 1024 1025 attnums = SQL::Function.new(:ANY, Sequel[:ind][:indkey]) 1026 1027 ds = metadata_dataset. 1028 from{pg_class.as(:tab)}. 1029 join(Sequel[:pg_index].as(:ind), :indrelid=>:oid). 1030 join(Sequel[:pg_class].as(:indc), :oid=>:indexrelid). 1031 join(Sequel[:pg_attribute].as(:att), :attrelid=>Sequel[:tab][:oid], :attnum=>attnums). 1032 left_join(Sequel[:pg_constraint].as(:con), :conname=>Sequel[:indc][:relname]). 1033 where{{ 1034 indc[:relkind]=>%w'i I', 1035 ind[:indisprimary]=>false, 1036 :indexprs=>nil, 1037 :indisvalid=>true}}. 1038 order(*order). 1039 select{[indc[:relname].as(:name), ind[:indisunique].as(:unique), att[:attname].as(:column), con[:condeferrable].as(:deferrable)]} 1040 1041 # :nocov: 1042 ds = ds.where(:indisready=>true) if server_version >= 80300 1043 ds = ds.where(:indislive=>true) if server_version >= 90300 1044 # :nocov: 1045 1046 ds 1047 end 1048 end
Dataset
used to retrieve foreign keys referencing a table
# File lib/sequel/adapters/shared/postgres.rb 931 def _reverse_foreign_key_list_ds 932 @_reverse_foreign_key_list_ds ||= __foreign_key_list_ds(true) 933 end
Dataset
used to get schema for tables
# File lib/sequel/adapters/shared/postgres.rb 1111 def _schema_ds 1112 @_schema_ds ||= begin 1113 ds = metadata_dataset.select{[ 1114 pg_attribute[:attname].as(:name), 1115 SQL::Cast.new(pg_attribute[:atttypid], :integer).as(:oid), 1116 SQL::Cast.new(basetype[:oid], :integer).as(:base_oid), 1117 SQL::Function.new(:col_description, pg_class[:oid], pg_attribute[:attnum]).as(:comment), 1118 SQL::Function.new(:format_type, basetype[:oid], pg_type[:typtypmod]).as(:db_base_type), 1119 SQL::Function.new(:format_type, pg_type[:oid], pg_attribute[:atttypmod]).as(:db_type), 1120 SQL::Function.new(:pg_get_expr, pg_attrdef[:adbin], pg_class[:oid]).as(:default), 1121 SQL::BooleanExpression.new(:NOT, pg_attribute[:attnotnull]).as(:allow_null), 1122 SQL::Function.new(:COALESCE, SQL::BooleanExpression.from_value_pairs(pg_attribute[:attnum] => SQL::Function.new(:ANY, pg_index[:indkey])), false).as(:primary_key), 1123 Sequel[:pg_type][:typtype], 1124 (~Sequel[Sequel[:elementtype][:oid]=>nil]).as(:is_array), 1125 ]}. 1126 from(:pg_class). 1127 join(:pg_attribute, :attrelid=>:oid). 1128 join(:pg_type, :oid=>:atttypid). 1129 left_outer_join(Sequel[:pg_type].as(:basetype), :oid=>:typbasetype). 1130 left_outer_join(Sequel[:pg_type].as(:elementtype), :typarray=>Sequel[:pg_type][:oid]). 1131 left_outer_join(:pg_attrdef, :adrelid=>Sequel[:pg_class][:oid], :adnum=>Sequel[:pg_attribute][:attnum]). 1132 left_outer_join(:pg_index, :indrelid=>Sequel[:pg_class][:oid], :indisprimary=>true). 1133 where{{pg_attribute[:attisdropped]=>false}}. 1134 where{pg_attribute[:attnum] > 0}. 1135 order{pg_attribute[:attnum]} 1136 1137 # :nocov: 1138 if server_version > 100000 1139 # :nocov: 1140 ds = ds.select_append{pg_attribute[:attidentity]} 1141 1142 # :nocov: 1143 if server_version > 120000 1144 # :nocov: 1145 ds = ds.select_append{Sequel.~(pg_attribute[:attgenerated]=>'').as(:generated)} 1146 end 1147 end 1148 1149 ds 1150 end 1151 end
Dataset
used to determine custom serial sequences for tables
# File lib/sequel/adapters/shared/postgres.rb 1051 def _select_custom_sequence_ds 1052 @_select_custom_sequence_ds ||= metadata_dataset. 1053 from{pg_class.as(:t)}. 1054 join(:pg_namespace, {:oid => :relnamespace}, :table_alias=>:name). 1055 join(:pg_attribute, {:attrelid => Sequel[:t][:oid]}, :table_alias=>:attr). 1056 join(:pg_attrdef, {:adrelid => :attrelid, :adnum => :attnum}, :table_alias=>:def). 1057 join(:pg_constraint, {:conrelid => :adrelid, Sequel[:cons][:conkey].sql_subscript(1) => :adnum}, :table_alias=>:cons). 1058 where{{cons[:contype] => 'p', pg_get_expr(self.def[:adbin], attr[:attrelid]) => /nextval/i}}. 1059 select{ 1060 expr = split_part(pg_get_expr(self.def[:adbin], attr[:attrelid]), "'", 2) 1061 [ 1062 name[:nspname].as(:schema), 1063 Sequel.case({{expr => /./} => substr(expr, strpos(expr, '.')+1)}, expr).as(:sequence) 1064 ] 1065 } 1066 end
Dataset
used to determine primary keys for tables
# File lib/sequel/adapters/shared/postgres.rb 1097 def _select_pk_ds 1098 @_select_pk_ds ||= metadata_dataset. 1099 from(:pg_class, :pg_attribute, :pg_index, :pg_namespace). 1100 where{[ 1101 [pg_class[:oid], pg_attribute[:attrelid]], 1102 [pg_class[:relnamespace], pg_namespace[:oid]], 1103 [pg_class[:oid], pg_index[:indrelid]], 1104 [pg_index[:indkey].sql_subscript(0), pg_attribute[:attnum]], 1105 [pg_index[:indisprimary], 't'] 1106 ]}. 1107 select{pg_attribute[:attname].as(:pk)} 1108 end
Dataset
used to determine normal serial sequences for tables
# File lib/sequel/adapters/shared/postgres.rb 1069 def _select_serial_sequence_ds 1070 @_serial_sequence_ds ||= metadata_dataset. 1071 from{[ 1072 pg_class.as(:seq), 1073 pg_attribute.as(:attr), 1074 pg_depend.as(:dep), 1075 pg_namespace.as(:name), 1076 pg_constraint.as(:cons), 1077 pg_class.as(:t) 1078 ]}. 1079 where{[ 1080 [seq[:oid], dep[:objid]], 1081 [seq[:relnamespace], name[:oid]], 1082 [seq[:relkind], 'S'], 1083 [attr[:attrelid], dep[:refobjid]], 1084 [attr[:attnum], dep[:refobjsubid]], 1085 [attr[:attrelid], cons[:conrelid]], 1086 [attr[:attnum], cons[:conkey].sql_subscript(1)], 1087 [attr[:attrelid], t[:oid]], 1088 [cons[:contype], 'p'] 1089 ]}. 1090 select{[ 1091 name[:nspname].as(:schema), 1092 seq[:relname].as(:sequence) 1093 ]} 1094 end
Internals of defer_constraints/immediate_constraints
# File lib/sequel/adapters/shared/postgres.rb 1154 def _set_constraints(type, opts) 1155 execute_ddl(_set_constraints_sql(type, opts), opts) 1156 end
SQL
to use for SET CONSTRAINTS
# File lib/sequel/adapters/shared/postgres.rb 1159 def _set_constraints_sql(type, opts) 1160 sql = String.new 1161 sql << "SET CONSTRAINTS " 1162 if constraints = opts[:constraints] 1163 dataset.send(:source_list_append, sql, Array(constraints)) 1164 else 1165 sql << "ALL" 1166 end 1167 sql << type 1168 end
Consider lock or statement timeout errors as evidence that the table exists but is locked.
# File lib/sequel/adapters/shared/postgres.rb 1172 def _table_exists?(ds) 1173 super 1174 rescue DatabaseError => e 1175 raise e unless /canceling statement due to (?:statement|lock) timeout/ =~ e.message 1176 end
# File lib/sequel/adapters/shared/postgres.rb 1178 def alter_table_add_column_sql(table, op) 1179 "ADD COLUMN#{' IF NOT EXISTS' if op[:if_not_exists]} #{column_definition_sql(op)}" 1180 end
# File lib/sequel/adapters/shared/postgres.rb 1182 def alter_table_alter_constraint_sql(table, op) 1183 sql = String.new 1184 sql << "ALTER CONSTRAINT #{quote_identifier(op[:name])}" 1185 1186 constraint_deferrable_sql_append(sql, op[:deferrable]) 1187 1188 case op[:enforced] 1189 when nil 1190 when false 1191 sql << " NOT ENFORCED" 1192 else 1193 sql << " ENFORCED" 1194 end 1195 1196 case op[:inherit] 1197 when nil 1198 when false 1199 sql << " NO INHERIT" 1200 else 1201 sql << " INHERIT" 1202 end 1203 1204 sql 1205 end
# File lib/sequel/adapters/shared/postgres.rb 1221 def alter_table_drop_column_sql(table, op) 1222 "DROP COLUMN #{'IF EXISTS ' if op[:if_exists]}#{quote_identifier(op[:name])}#{' CASCADE' if op[:cascade]}" 1223 end
# File lib/sequel/adapters/shared/postgres.rb 1207 def alter_table_generator_class 1208 Postgres::AlterTableGenerator 1209 end
# File lib/sequel/adapters/shared/postgres.rb 1211 def alter_table_set_column_type_sql(table, op) 1212 s = super 1213 if using = op[:using] 1214 using = Sequel::LiteralString.new(using) if using.is_a?(String) 1215 s += ' USING ' 1216 s << literal(using) 1217 end 1218 s 1219 end
# File lib/sequel/adapters/shared/postgres.rb 1225 def alter_table_validate_constraint_sql(table, op) 1226 "VALIDATE CONSTRAINT #{quote_identifier(op[:name])}" 1227 end
If the :synchronous option is given and non-nil, set synchronous_commit appropriately. Valid values for the :synchronous option are true, :on, false, :off, :local, and :remote_write.
# File lib/sequel/adapters/shared/postgres.rb 1232 def begin_new_transaction(conn, opts) 1233 super 1234 if opts.has_key?(:synchronous) 1235 case sync = opts[:synchronous] 1236 when true 1237 sync = :on 1238 when false 1239 sync = :off 1240 when nil 1241 return 1242 end 1243 1244 log_connection_execute(conn, "SET LOCAL synchronous_commit = #{sync}") 1245 end 1246 end
Set the READ ONLY transaction setting per savepoint, as PostgreSQL supports that.
# File lib/sequel/adapters/shared/postgres.rb 1249 def begin_savepoint(conn, opts) 1250 super 1251 1252 unless (read_only = opts[:read_only]).nil? 1253 log_connection_execute(conn, "SET TRANSACTION READ #{read_only ? 'ONLY' : 'WRITE'}") 1254 end 1255 end
# File lib/sequel/adapters/shared/postgres.rb 1370 def column_definition_add_references_sql(sql, column) 1371 super 1372 if column[:not_enforced] 1373 sql << " NOT ENFORCED" 1374 end 1375 end
Literalize non-String collate options. This is because unquoted collatations are folded to lowercase, and PostgreSQL used mixed case or capitalized collations.
# File lib/sequel/adapters/shared/postgres.rb 1259 def column_definition_collate_sql(sql, column) 1260 if collate = column[:collate] 1261 collate = literal(collate) unless collate.is_a?(String) 1262 sql << " COLLATE #{collate}" 1263 end 1264 end
Support identity columns, but only use the identity SQL
syntax if no default value is given.
# File lib/sequel/adapters/shared/postgres.rb 1268 def column_definition_default_sql(sql, column) 1269 super 1270 if !column[:serial] && !['smallserial', 'serial', 'bigserial'].include?(column[:type].to_s) && !column[:default] 1271 if (identity = column[:identity]) 1272 sql << " GENERATED " 1273 sql << (identity == :always ? "ALWAYS" : "BY DEFAULT") 1274 sql << " AS IDENTITY" 1275 elsif (generated = column[:generated_always_as]) 1276 sql << " GENERATED ALWAYS AS (#{literal(generated)}) STORED" 1277 end 1278 end 1279 end
Handle PostgreSQL specific default format.
# File lib/sequel/adapters/shared/postgres.rb 1282 def column_schema_normalize_default(default, type) 1283 if m = /\A(?:B?('.*')::[^']+|\((-?\d+(?:\.\d+)?)\))\z/.match(default) 1284 default = m[1] || m[2] 1285 end 1286 super(default, type) 1287 end
PostgreSQL can’t combine rename_column operations, and it can combine validate_constraint and alter_constraint operations.
# File lib/sequel/adapters/shared/postgres.rb 1301 def combinable_alter_table_op?(op) 1302 (super || op[:op] == :validate_constraint || op[:op] == :alter_constraint) && op[:op] != :rename_column 1303 end
If the :prepare option is given and we aren’t in a savepoint, prepare the transaction for a two-phase commit.
# File lib/sequel/adapters/shared/postgres.rb 1291 def commit_transaction(conn, opts=OPTS) 1292 if (s = opts[:prepare]) && savepoint_level(conn) <= 1 1293 log_connection_execute(conn, "PREPARE TRANSACTION #{literal(s)}") 1294 else 1295 super 1296 end 1297 end
The SQL
queries to execute when starting a new connection.
# File lib/sequel/adapters/shared/postgres.rb 1307 def connection_configuration_sqls(opts=@opts) 1308 sqls = [] 1309 1310 sqls << "SET standard_conforming_strings = ON" if typecast_value_boolean(opts.fetch(:force_standard_strings, true)) 1311 1312 cmm = opts.fetch(:client_min_messages, :warning) 1313 if cmm && !cmm.to_s.empty? 1314 cmm = cmm.to_s.upcase.strip 1315 unless VALID_CLIENT_MIN_MESSAGES.include?(cmm) 1316 raise Error, "Unsupported client_min_messages setting: #{cmm}" 1317 end 1318 sqls << "SET client_min_messages = '#{cmm.to_s.upcase}'" 1319 end 1320 1321 if search_path = opts[:search_path] 1322 case search_path 1323 when String 1324 search_path = search_path.split(",").map(&:strip) 1325 when Array 1326 # nil 1327 else 1328 raise Error, "unrecognized value for :search_path option: #{search_path.inspect}" 1329 end 1330 sqls << "SET search_path = #{search_path.map{|s| "\"#{s.gsub('"', '""')}\""}.join(',')}" 1331 end 1332 1333 sqls 1334 end
Handle PostgreSQL-specific constraint features.
# File lib/sequel/adapters/shared/postgres.rb 1337 def constraint_definition_sql(constraint) 1338 case type = constraint[:type] 1339 when :exclude 1340 elements = constraint[:elements].map{|c, op| "#{literal(c)} WITH #{op}"}.join(', ') 1341 sql = String.new 1342 sql << "#{"CONSTRAINT #{quote_identifier(constraint[:name])} " if constraint[:name]}EXCLUDE USING #{constraint[:using]||'gist'} (#{elements})#{" WHERE #{filter_expr(constraint[:where])}" if constraint[:where]}" 1343 constraint_deferrable_sql_append(sql, constraint[:deferrable]) 1344 sql 1345 when :primary_key, :unique 1346 if using_index = constraint[:using_index] 1347 sql = String.new 1348 sql << "CONSTRAINT #{quote_identifier(constraint[:name])} " if constraint[:name] 1349 if type == :primary_key 1350 sql << primary_key_constraint_sql_fragment(constraint) 1351 else 1352 sql << unique_constraint_sql_fragment(constraint) 1353 end 1354 sql << " USING INDEX " << quote_identifier(using_index) 1355 else 1356 super 1357 end 1358 else # when :foreign_key, :check 1359 sql = super 1360 if constraint[:not_enforced] 1361 sql << " NOT ENFORCED" 1362 end 1363 if constraint[:not_valid] 1364 sql << " NOT VALID" 1365 end 1366 sql 1367 end 1368 end
SQL
for doing fast table insert from stdin.
# File lib/sequel/adapters/shared/postgres.rb 1406 def copy_into_sql(table, opts) 1407 sql = String.new 1408 sql << "COPY #{literal(table)}" 1409 if cols = opts[:columns] 1410 sql << literal(Array(cols)) 1411 end 1412 sql << " FROM STDIN" 1413 if opts[:options] || opts[:format] 1414 sql << " (" 1415 sql << "FORMAT #{opts[:format]}" if opts[:format] 1416 sql << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options] 1417 sql << ')' 1418 end 1419 sql 1420 end
SQL
for doing fast table output to stdout.
# File lib/sequel/adapters/shared/postgres.rb 1423 def copy_table_sql(table, opts) 1424 if table.is_a?(String) 1425 table 1426 else 1427 if opts[:options] || opts[:format] 1428 options = String.new 1429 options << " (" 1430 options << "FORMAT #{opts[:format]}" if opts[:format] 1431 options << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options] 1432 options << ')' 1433 end 1434 table = if table.is_a?(::Sequel::Dataset) 1435 "(#{table.sql})" 1436 else 1437 literal(table) 1438 end 1439 "COPY #{table} TO STDOUT#{options}" 1440 end 1441 end
SQL
statement to create database function.
# File lib/sequel/adapters/shared/postgres.rb 1444 def create_function_sql(name, definition, opts=OPTS) 1445 args = opts[:args] 1446 in_out = %w'OUT INOUT' 1447 if (!opts[:args].is_a?(Array) || !opts[:args].any?{|a| Array(a).length == 3 && in_out.include?(a[2].to_s)}) 1448 returns = opts[:returns] || 'void' 1449 end 1450 language = opts[:language] || 'SQL' 1451 <<-END 1452 CREATE#{' OR REPLACE' if opts[:replace]} FUNCTION #{name}#{sql_function_args(args)} 1453 #{"RETURNS #{returns}" if returns} 1454 LANGUAGE #{language} 1455 #{opts[:behavior].to_s.upcase if opts[:behavior]} 1456 #{'STRICT' if opts[:strict]} 1457 #{'SECURITY DEFINER' if opts[:security_definer]} 1458 #{"PARALLEL #{opts[:parallel].to_s.upcase}" if opts[:parallel]} 1459 #{"COST #{opts[:cost]}" if opts[:cost]} 1460 #{"ROWS #{opts[:rows]}" if opts[:rows]} 1461 #{opts[:set].map{|k,v| " SET #{k} = #{v}"}.join("\n") if opts[:set]} 1462 AS #{literal(definition.to_s)}#{", #{literal(opts[:link_symbol].to_s)}" if opts[:link_symbol]} 1463 END 1464 end
SQL
for creating a procedural language.
# File lib/sequel/adapters/shared/postgres.rb 1467 def create_language_sql(name, opts=OPTS) 1468 "CREATE#{' OR REPLACE' if opts[:replace] && server_version >= 90000}#{' TRUSTED' if opts[:trusted]} LANGUAGE #{name}#{" HANDLER #{opts[:handler]}" if opts[:handler]}#{" VALIDATOR #{opts[:validator]}" if opts[:validator]}" 1469 end
Create a partition of another table, used when the create_table
with the :partition_of option is given.
# File lib/sequel/adapters/shared/postgres.rb 1473 def create_partition_of_table_from_generator(name, generator, options) 1474 execute_ddl(create_partition_of_table_sql(name, generator, options)) 1475 end
SQL
for creating a partition of another table.
# File lib/sequel/adapters/shared/postgres.rb 1478 def create_partition_of_table_sql(name, generator, options) 1479 sql = create_table_prefix_sql(name, options).dup 1480 1481 sql << " PARTITION OF #{quote_schema_table(options[:partition_of])}" 1482 1483 case generator.partition_type 1484 when :range 1485 from, to = generator.range 1486 sql << " FOR VALUES FROM #{literal(from)} TO #{literal(to)}" 1487 when :list 1488 sql << " FOR VALUES IN #{literal(generator.list)}" 1489 when :hash 1490 mod, remainder = generator.hash_values 1491 sql << " FOR VALUES WITH (MODULUS #{literal(mod)}, REMAINDER #{literal(remainder)})" 1492 else # when :default 1493 sql << " DEFAULT" 1494 end 1495 1496 sql << create_table_suffix_sql(name, options) 1497 1498 sql 1499 end
SQL
for creating a schema.
# File lib/sequel/adapters/shared/postgres.rb 1502 def create_schema_sql(name, opts=OPTS) 1503 "CREATE SCHEMA #{'IF NOT EXISTS ' if opts[:if_not_exists]}#{quote_identifier(name)}#{" AUTHORIZATION #{literal(opts[:owner])}" if opts[:owner]}" 1504 end
# File lib/sequel/adapters/shared/postgres.rb 1560 def create_table_as_sql(name, sql, options) 1561 result = create_table_prefix_sql name, options 1562 if on_commit = options[:on_commit] 1563 result += " ON COMMIT #{ON_COMMIT[on_commit]}" 1564 end 1565 result += " AS #{sql}" 1566 end
# File lib/sequel/adapters/shared/postgres.rb 1568 def create_table_generator_class 1569 Postgres::CreateTableGenerator 1570 end
DDL statement for creating a table with the given name, columns, and options
# File lib/sequel/adapters/shared/postgres.rb 1507 def create_table_prefix_sql(name, options) 1508 prefix_sql = if options[:temp] 1509 raise(Error, "can't provide both :temp and :unlogged to create_table") if options[:unlogged] 1510 raise(Error, "can't provide both :temp and :foreign to create_table") if options[:foreign] 1511 temporary_table_sql 1512 elsif options[:foreign] 1513 raise(Error, "can't provide both :foreign and :unlogged to create_table") if options[:unlogged] 1514 'FOREIGN ' 1515 elsif options.fetch(:unlogged){typecast_value_boolean(@opts[:unlogged_tables_default])} 1516 'UNLOGGED ' 1517 end 1518 1519 "CREATE #{prefix_sql}TABLE#{' IF NOT EXISTS' if options[:if_not_exists]} #{create_table_table_name_sql(name, options)}" 1520 end
SQL
for creating a table with PostgreSQL specific options
# File lib/sequel/adapters/shared/postgres.rb 1523 def create_table_sql(name, generator, options) 1524 "#{super}#{create_table_suffix_sql(name, options)}" 1525 end
Handle various PostgreSQl specific table extensions such as inheritance, partitioning, tablespaces, and foreign tables.
# File lib/sequel/adapters/shared/postgres.rb 1529 def create_table_suffix_sql(name, options) 1530 sql = String.new 1531 1532 if inherits = options[:inherits] 1533 sql << " INHERITS (#{Array(inherits).map{|t| quote_schema_table(t)}.join(', ')})" 1534 end 1535 1536 if partition_by = options[:partition_by] 1537 sql << " PARTITION BY #{options[:partition_type]||'RANGE'} #{literal(Array(partition_by))}" 1538 end 1539 1540 if on_commit = options[:on_commit] 1541 raise(Error, "can't provide :on_commit without :temp to create_table") unless options[:temp] 1542 raise(Error, "unsupported on_commit option: #{on_commit.inspect}") unless ON_COMMIT.has_key?(on_commit) 1543 sql << " ON COMMIT #{ON_COMMIT[on_commit]}" 1544 end 1545 1546 if tablespace = options[:tablespace] 1547 sql << " TABLESPACE #{quote_identifier(tablespace)}" 1548 end 1549 1550 if server = options[:foreign] 1551 sql << " SERVER #{quote_identifier(server)}" 1552 if foreign_opts = options[:options] 1553 sql << " OPTIONS (#{foreign_opts.map{|k, v| "#{k} #{literal(v.to_s)}"}.join(', ')})" 1554 end 1555 end 1556 1557 sql 1558 end
SQL
for creating a database trigger.
# File lib/sequel/adapters/shared/postgres.rb 1573 def create_trigger_sql(table, name, function, opts=OPTS) 1574 events = opts[:events] ? Array(opts[:events]) : [:insert, :update, :delete] 1575 whence = opts[:after] ? 'AFTER' : 'BEFORE' 1576 if filter = opts[:when] 1577 raise Error, "Trigger conditions are not supported for this database" unless supports_trigger_conditions? 1578 filter = " WHEN #{filter_expr(filter)}" 1579 end 1580 "CREATE #{'OR REPLACE ' if opts[:replace]}TRIGGER #{name} #{whence} #{events.map{|e| e.to_s.upcase}.join(' OR ')} ON #{quote_schema_table(table)}#{' FOR EACH ROW' if opts[:each_row]}#{filter} EXECUTE PROCEDURE #{function}(#{Array(opts[:args]).map{|a| literal(a)}.join(', ')})" 1581 end
DDL fragment for initial part of CREATE VIEW statement
# File lib/sequel/adapters/shared/postgres.rb 1584 def create_view_prefix_sql(name, options) 1585 sql = create_view_sql_append_columns("CREATE #{'OR REPLACE 'if options[:replace]}#{'TEMPORARY 'if options[:temp]}#{'RECURSIVE ' if options[:recursive]}#{'MATERIALIZED ' if options[:materialized]}VIEW #{quote_schema_table(name)}", options[:columns] || options[:recursive]) 1586 1587 if options[:security_invoker] 1588 sql += " WITH (security_invoker)" 1589 end 1590 1591 if tablespace = options[:tablespace] 1592 sql += " TABLESPACE #{quote_identifier(tablespace)}" 1593 end 1594 1595 sql 1596 end
# File lib/sequel/adapters/shared/postgres.rb 1401 def database_error_regexps 1402 DATABASE_ERROR_REGEXPS 1403 end
# File lib/sequel/adapters/shared/postgres.rb 1377 def database_specific_error_class_from_sqlstate(sqlstate) 1378 if sqlstate == '23P01' 1379 ExclusionConstraintViolation 1380 elsif sqlstate == '40P01' 1381 SerializationFailure 1382 elsif sqlstate == '55P03' 1383 DatabaseLockTimeout 1384 else 1385 super 1386 end 1387 end
SQL
for dropping a function from the database.
# File lib/sequel/adapters/shared/postgres.rb 1599 def drop_function_sql(name, opts=OPTS) 1600 "DROP FUNCTION#{' IF EXISTS' if opts[:if_exists]} #{name}#{sql_function_args(opts[:args])}#{' CASCADE' if opts[:cascade]}" 1601 end
Support :if_exists, :cascade, and :concurrently options.
# File lib/sequel/adapters/shared/postgres.rb 1604 def drop_index_sql(table, op) 1605 sch, _ = schema_and_table(table) 1606 "DROP INDEX#{' CONCURRENTLY' if op[:concurrently]}#{' IF EXISTS' if op[:if_exists]} #{"#{quote_identifier(sch)}." if sch}#{quote_identifier(op[:name] || default_index_name(table, op[:columns]))}#{' CASCADE' if op[:cascade]}" 1607 end
SQL
for dropping a procedural language from the database.
# File lib/sequel/adapters/shared/postgres.rb 1610 def drop_language_sql(name, opts=OPTS) 1611 "DROP LANGUAGE#{' IF EXISTS' if opts[:if_exists]} #{name}#{' CASCADE' if opts[:cascade]}" 1612 end
SQL
for dropping a schema from the database.
# File lib/sequel/adapters/shared/postgres.rb 1615 def drop_schema_sql(name, opts=OPTS) 1616 "DROP SCHEMA#{' IF EXISTS' if opts[:if_exists]} #{quote_identifier(name)}#{' CASCADE' if opts[:cascade]}" 1617 end
Support :foreign tables
# File lib/sequel/adapters/shared/postgres.rb 1625 def drop_table_sql(name, options) 1626 "DROP#{' FOREIGN' if options[:foreign]} TABLE#{' IF EXISTS' if options[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if options[:cascade]}" 1627 end
SQL
for dropping a trigger from the database.
# File lib/sequel/adapters/shared/postgres.rb 1620 def drop_trigger_sql(table, name, opts=OPTS) 1621 "DROP TRIGGER#{' IF EXISTS' if opts[:if_exists]} #{name} ON #{quote_schema_table(table)}#{' CASCADE' if opts[:cascade]}" 1622 end
SQL
for dropping a view from the database.
# File lib/sequel/adapters/shared/postgres.rb 1630 def drop_view_sql(name, opts=OPTS) 1631 "DROP #{'MATERIALIZED ' if opts[:materialized]}VIEW#{' IF EXISTS' if opts[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if opts[:cascade]}" 1632 end
If opts includes a :schema option, use it, otherwise restrict the filter to only the currently visible schemas.
# File lib/sequel/adapters/shared/postgres.rb 1636 def filter_schema(ds, opts) 1637 expr = if schema = opts[:schema] 1638 if schema.is_a?(SQL::Identifier) 1639 schema.value.to_s 1640 else 1641 schema.to_s 1642 end 1643 else 1644 Sequel.function(:any, Sequel.function(:current_schemas, false)) 1645 end 1646 ds.where{{pg_namespace[:nspname]=>expr}} 1647 end
# File lib/sequel/adapters/shared/postgres.rb 1649 def index_definition_sql(table_name, index) 1650 cols = index[:columns] 1651 index_name = index[:name] || default_index_name(table_name, cols) 1652 1653 expr = if o = index[:opclass] 1654 "(#{Array(cols).map{|c| "#{literal(c)} #{o}"}.join(', ')})" 1655 else 1656 literal(Array(cols)) 1657 end 1658 1659 if_not_exists = " IF NOT EXISTS" if index[:if_not_exists] 1660 unique = "UNIQUE " if index[:unique] 1661 index_type = index[:type] 1662 filter = index[:where] || index[:filter] 1663 filter = " WHERE #{filter_expr(filter)}" if filter 1664 nulls_distinct = " NULLS#{' NOT' if index[:nulls_distinct] == false} DISTINCT" unless index[:nulls_distinct].nil? 1665 1666 case index_type 1667 when :full_text 1668 expr = "(to_tsvector(#{literal(index[:language] || 'simple')}::regconfig, #{literal(dataset.send(:full_text_string_join, cols))}))" 1669 index_type = index[:index_type] || :gin 1670 when :spatial 1671 index_type = :gist 1672 end 1673 1674 "CREATE #{unique}INDEX#{' CONCURRENTLY' if index[:concurrently]}#{if_not_exists} #{quote_identifier(index_name)} ON #{quote_schema_table(table_name)} #{"USING #{index_type} " if index_type}#{expr}#{" INCLUDE #{literal(Array(index[:include]))}" if index[:include]}#{nulls_distinct}#{" TABLESPACE #{quote_identifier(index[:tablespace])}" if index[:tablespace]}#{filter}" 1675 end
Setup datastructures shared by all postgres adapters.
# File lib/sequel/adapters/shared/postgres.rb 1678 def initialize_postgres_adapter 1679 @primary_keys = {} 1680 @primary_key_sequences = {} 1681 @supported_types = {} 1682 procs = @conversion_procs = CONVERSION_PROCS.dup 1683 procs[1184] = procs[1114] = method(:to_application_timestamp) 1684 end
Backbone of the tables and views support.
# File lib/sequel/adapters/shared/postgres.rb 1687 def pg_class_relname(type, opts) 1688 ds = metadata_dataset.from(:pg_class).where(:relkind=>type).select(:relname).server(opts[:server]).join(:pg_namespace, :oid=>:relnamespace) 1689 ds = filter_schema(ds, opts) 1690 m = output_identifier_meth 1691 if defined?(yield) 1692 yield(ds) 1693 elsif opts[:qualify] 1694 ds.select_append{pg_namespace[:nspname]}.map{|r| Sequel.qualify(m.call(r[:nspname]).to_s, m.call(r[:relname]).to_s)} 1695 else 1696 ds.map{|r| m.call(r[:relname])} 1697 end 1698 end
Return an expression the oid for the table expr. Used by the metadata parsing code to disambiguate unqualified tables.
# File lib/sequel/adapters/shared/postgres.rb 1702 def regclass_oid(expr, opts=OPTS) 1703 if expr.is_a?(String) && !expr.is_a?(LiteralString) 1704 expr = Sequel.identifier(expr) 1705 end 1706 1707 sch, table = schema_and_table(expr) 1708 sch ||= opts[:schema] 1709 if sch 1710 expr = Sequel.qualify(sch, table) 1711 end 1712 1713 expr = if ds = opts[:dataset] 1714 ds.literal(expr) 1715 else 1716 literal(expr) 1717 end 1718 1719 Sequel.cast(expr.to_s,:regclass).cast(:oid) 1720 end
Remove the cached entries for primary keys and sequences when a table is changed.
# File lib/sequel/adapters/shared/postgres.rb 1723 def remove_cached_schema(table) 1724 tab = quote_schema_table(table) 1725 Sequel.synchronize do 1726 @primary_keys.delete(tab) 1727 @primary_key_sequences.delete(tab) 1728 end 1729 super 1730 end
SQL
DDL statement for renaming a table. PostgreSQL doesn’t allow you to change a table’s schema in a rename table operation, so specifying a new schema in new_name will not have an effect.
# File lib/sequel/adapters/shared/postgres.rb 1734 def rename_table_sql(name, new_name) 1735 "ALTER TABLE #{quote_schema_table(name)} RENAME TO #{quote_identifier(schema_and_table(new_name).last)}" 1736 end
The schema :type entry to use for array types.
# File lib/sequel/adapters/shared/postgres.rb 1751 def schema_array_type(db_type) 1752 :array 1753 end
Handle interval and citext types.
# File lib/sequel/adapters/shared/postgres.rb 1739 def schema_column_type(db_type) 1740 case db_type 1741 when /\Ainterval\z/i 1742 :interval 1743 when /\Acitext\z/i 1744 :string 1745 else 1746 super 1747 end 1748 end
The schema :type entry to use for row/composite types.
# File lib/sequel/adapters/shared/postgres.rb 1756 def schema_composite_type(db_type) 1757 :composite 1758 end
The schema :type entry to use for enum types.
# File lib/sequel/adapters/shared/postgres.rb 1761 def schema_enum_type(db_type) 1762 :enum 1763 end
The schema :type entry to use for multirange types.
# File lib/sequel/adapters/shared/postgres.rb 1771 def schema_multirange_type(db_type) 1772 :multirange 1773 end
The dataset used for parsing table schemas, using the pg_* system catalogs.
# File lib/sequel/adapters/shared/postgres.rb 1788 def schema_parse_table(table_name, opts) 1789 m = output_identifier_meth(opts[:dataset]) 1790 1791 _schema_ds.where_all(Sequel[:pg_class][:oid]=>regclass_oid(table_name, opts)).map do |row| 1792 row[:default] = nil if blank_object?(row[:default]) 1793 if row[:base_oid] 1794 row[:domain_oid] = row[:oid] 1795 row[:oid] = row.delete(:base_oid) 1796 row[:db_domain_type] = row[:db_type] 1797 row[:db_type] = row.delete(:db_base_type) 1798 else 1799 row.delete(:base_oid) 1800 row.delete(:db_base_type) 1801 end 1802 1803 db_type = row[:db_type] 1804 row[:type] = if row.delete(:is_array) 1805 schema_array_type(db_type) 1806 else 1807 send(TYPTYPE_METHOD_MAP[row.delete(:typtype)], db_type) 1808 end 1809 identity = row.delete(:attidentity) 1810 if row[:primary_key] 1811 row[:auto_increment] = !!(row[:default] =~ /\A(?:nextval)/i) || identity == 'a' || identity == 'd' 1812 end 1813 1814 # :nocov: 1815 if server_version >= 90600 1816 # :nocov: 1817 case row[:oid] 1818 when 1082 1819 row[:min_value] = MIN_DATE 1820 row[:max_value] = MAX_DATE 1821 when 1184, 1114 1822 if Sequel.datetime_class == Time 1823 row[:min_value] = MIN_TIMESTAMP 1824 row[:max_value] = MAX_TIMESTAMP 1825 end 1826 end 1827 end 1828 1829 [m.call(row.delete(:name)), row] 1830 end 1831 end
The schema :type entry to use for range types.
# File lib/sequel/adapters/shared/postgres.rb 1766 def schema_range_type(db_type) 1767 :range 1768 end
Set the transaction isolation level on the given connection
# File lib/sequel/adapters/shared/postgres.rb 1834 def set_transaction_isolation(conn, opts) 1835 level = opts.fetch(:isolation, transaction_isolation_level) 1836 read_only = opts[:read_only] 1837 deferrable = opts[:deferrable] 1838 if level || !read_only.nil? || !deferrable.nil? 1839 sql = String.new 1840 sql << "SET TRANSACTION" 1841 sql << " ISOLATION LEVEL #{Sequel::Database::TRANSACTION_ISOLATION_LEVELS[level]}" if level 1842 sql << " READ #{read_only ? 'ONLY' : 'WRITE'}" unless read_only.nil? 1843 sql << " #{'NOT ' unless deferrable}DEFERRABLE" unless deferrable.nil? 1844 log_connection_execute(conn, sql) 1845 end 1846 end
Turns an array of argument specifiers into an SQL
fragment used for function arguments. See create_function_sql.
# File lib/sequel/adapters/shared/postgres.rb 1849 def sql_function_args(args) 1850 "(#{Array(args).map{|a| Array(a).reverse.join(' ')}.join(', ')})" 1851 end
PostgreSQL can combine multiple alter table ops into a single query.
# File lib/sequel/adapters/shared/postgres.rb 1854 def supports_combining_alter_table_ops? 1855 true 1856 end
PostgreSQL supports CREATE OR REPLACE VIEW.
# File lib/sequel/adapters/shared/postgres.rb 1859 def supports_create_or_replace_view? 1860 true 1861 end
Handle bigserial type if :serial option is present
# File lib/sequel/adapters/shared/postgres.rb 1864 def type_literal_generic_bignum_symbol(column) 1865 column[:serial] ? :bigserial : super 1866 end
PostgreSQL uses the bytea data type for blobs
# File lib/sequel/adapters/shared/postgres.rb 1869 def type_literal_generic_file(column) 1870 :bytea 1871 end
Handle serial type if :serial option is present
# File lib/sequel/adapters/shared/postgres.rb 1874 def type_literal_generic_integer(column) 1875 column[:serial] ? :serial : super 1876 end
PostgreSQL prefers the text datatype. If a fixed size is requested, the char type is used. If the text type is specifically disallowed or there is a size specified, use the varchar type. Otherwise use the text type.
# File lib/sequel/adapters/shared/postgres.rb 1882 def type_literal_generic_string(column) 1883 if column[:text] 1884 :text 1885 elsif column[:fixed] 1886 "char(#{column[:size]||default_string_column_size})" 1887 elsif column[:text] == false || column[:size] 1888 "varchar(#{column[:size]||default_string_column_size})" 1889 else 1890 :text 1891 end 1892 end
PostgreSQL 9.4+ supports views with check option.
# File lib/sequel/adapters/shared/postgres.rb 1895 def view_with_check_option_support 1896 # :nocov: 1897 :local if server_version >= 90400 1898 # :nocov: 1899 end