~ubuntu-branches/ubuntu/hardy/postgresql-8.4/hardy-backports

SELECT * FROM array_index_op_test WHERE t <@ '{AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}' ORDER BY seqno;

168

SELECT * FROM array_index_op_test WHERE t = '{AAAAAAAAAA646,A87088}' ORDER BY seqno;

169

170

-- Repeat some of the above tests but exercising bitmapscans instead

171

SET enable_indexscan = OFF;

172

SET enable_bitmapscan = ON;

173

174

SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno;

175

SELECT * FROM array_index_op_test WHERE i && '{32}' ORDER BY seqno;

176

SELECT * FROM array_index_op_test WHERE i @> '{17}' ORDER BY seqno;

177

SELECT * FROM array_index_op_test WHERE i && '{17}' ORDER BY seqno;

178

SELECT * FROM array_index_op_test WHERE i @> '{32,17}' ORDER BY seqno;

179

SELECT * FROM array_index_op_test WHERE i && '{32,17}' ORDER BY seqno;

180

SELECT * FROM array_index_op_test WHERE i <@ '{38,34,32,89}' ORDER BY seqno;

181

SELECT * FROM array_index_op_test WHERE i = '{47,77}' ORDER BY seqno;

182

183

-- And try it with a multicolumn GIN index

184

185

DROP INDEX intarrayidx, textarrayidx;

186

187

CREATE INDEX botharrayidx ON array_index_op_test USING gin (i, t);

188

189

SET enable_seqscan = OFF;

190

SET enable_indexscan = ON;

191

SET enable_bitmapscan = OFF;

192

193

SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno;

194

SELECT * FROM array_index_op_test WHERE i && '{32}' ORDER BY seqno;

195

SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAA80240}' ORDER BY seqno;

196

SELECT * FROM array_index_op_test WHERE t && '{AAAAAAA80240}' ORDER BY seqno;

197

SELECT * FROM array_index_op_test WHERE i @> '{32}' AND t && '{AAAAAAA80240}' ORDER BY seqno;

198

SELECT * FROM array_index_op_test WHERE i && '{32}' AND t @> '{AAAAAAA80240}' ORDER BY seqno;

199

200

SET enable_indexscan = OFF;

201

SET enable_bitmapscan = ON;

202

203

SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno;

204

SELECT * FROM array_index_op_test WHERE i && '{32}' ORDER BY seqno;

205

SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAA80240}' ORDER BY seqno;

206

SELECT * FROM array_index_op_test WHERE t && '{AAAAAAA80240}' ORDER BY seqno;

207

SELECT * FROM array_index_op_test WHERE i @> '{32}' AND t && '{AAAAAAA80240}' ORDER BY seqno;

208

SELECT * FROM array_index_op_test WHERE i && '{32}' AND t @> '{AAAAAAA80240}' ORDER BY seqno;

209

210

RESET enable_seqscan;

211

RESET enable_indexscan;

212

RESET enable_bitmapscan;

213

214

215

-- HASH

216

217

CREATE INDEX hash_i4_index ON hash_i4_heap USING hash (random int4_ops);

218

219

CREATE INDEX hash_name_index ON hash_name_heap USING hash (random name_ops);

220

221

CREATE INDEX hash_txt_index ON hash_txt_heap USING hash (random text_ops);

222

223

CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops);

224

225

-- CREATE INDEX hash_ovfl_index ON hash_ovfl_heap USING hash (x int4_ops);

226

227

228

229

-- Test functional index

230

231

CREATE TABLE func_index_heap (f1 text, f2 text);

232

CREATE UNIQUE INDEX func_index_index on func_index_heap (textcat(f1,f2));

233

234

INSERT INTO func_index_heap VALUES('ABC','DEF');

235

INSERT INTO func_index_heap VALUES('AB','CDEFG');

236

INSERT INTO func_index_heap VALUES('QWE','RTY');

237

-- this should fail because of unique index:

238

INSERT INTO func_index_heap VALUES('ABCD', 'EF');

239

-- but this shouldn't:

240

INSERT INTO func_index_heap VALUES('QWERTY');

241

242

243

244

-- Same test, expressional index

245

246

DROP TABLE func_index_heap;

247

CREATE TABLE func_index_heap (f1 text, f2 text);

248

CREATE UNIQUE INDEX func_index_index on func_index_heap ((f1 || f2) text_ops);

249

250

INSERT INTO func_index_heap VALUES('ABC','DEF');

251

INSERT INTO func_index_heap VALUES('AB','CDEFG');

252

INSERT INTO func_index_heap VALUES('QWE','RTY');

253

-- this should fail because of unique index:

254

INSERT INTO func_index_heap VALUES('ABCD', 'EF');

255

-- but this shouldn't:

256

INSERT INTO func_index_heap VALUES('QWERTY');

257

258

259

-- Also try building functional, expressional, and partial indexes on

260

-- tables that already contain data.

261

262

create unique index hash_f8_index_1 on hash_f8_heap(abs(random));

263

create unique index hash_f8_index_2 on hash_f8_heap((seqno + 1), random);

264

create unique index hash_f8_index_3 on hash_f8_heap(random) where seqno > 1000;

265

266

267

-- Try some concurrent index builds

268

269

-- Unfortunately this only tests about half the code paths because there are

270

-- no concurrent updates happening to the table at the same time.

271

272

CREATE TABLE concur_heap (f1 text, f2 text);

273

-- empty table

274

CREATE INDEX CONCURRENTLY concur_index1 ON concur_heap(f2,f1);

275

INSERT INTO concur_heap VALUES ('a','b');

276

INSERT INTO concur_heap VALUES ('b','b');

277

-- unique index

278

CREATE UNIQUE INDEX CONCURRENTLY concur_index2 ON concur_heap(f1);

279

-- check if constraint is set up properly to be enforced

280

INSERT INTO concur_heap VALUES ('b','x');

281

-- check if constraint is enforced properly at build time

282

CREATE UNIQUE INDEX CONCURRENTLY concur_index3 ON concur_heap(f2);

283

-- test that expression indexes and partial indexes work concurrently

284

CREATE INDEX CONCURRENTLY concur_index4 on concur_heap(f2) WHERE f1='a';

285

CREATE INDEX CONCURRENTLY concur_index5 on concur_heap(f2) WHERE f1='x';

286

CREATE INDEX CONCURRENTLY concur_index6 on concur_heap((f2||f1));

287

288

-- You can't do a concurrent index build in a transaction

289

BEGIN;

290

CREATE INDEX CONCURRENTLY concur_index7 ON concur_heap(f1);

291

COMMIT;

292

293

-- But you can do a regular index build in a transaction

294

BEGIN;

295

CREATE INDEX std_index on concur_heap(f2);

296

COMMIT;

297

298

-- check to make sure that the failed indexes were cleaned up properly and the

299

-- successful indexes are created properly. Notably that they do NOT have the

300

-- "invalid" flag set.

301

302

\d concur_heap

303

304

DROP TABLE concur_heap;

305

306

307

-- Tests for IS NULL with b-tree indexes

308

309

310

SELECT unique1, unique2 INTO onek_with_null FROM onek;

311

INSERT INTO onek_with_null (unique1,unique2) VALUES (NULL, -1), (NULL, NULL);

312

CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2,unique1);

313

314

SET enable_seqscan = OFF;

315

SET enable_indexscan = ON;

316

SET enable_bitmapscan = ON;

317

318

SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;

319

SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;

320

321

DROP INDEX onek_nulltest;

322

323

CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 desc,unique1);

324

325

SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;

326

SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;

327

328

DROP INDEX onek_nulltest;

329

330

CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 desc nulls last,unique1);

331

332

SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;

333

SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;

334

335

DROP INDEX onek_nulltest;

336

337

CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 nulls first,unique1);

338

339

SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;

340

SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;

341

342

RESET enable_seqscan;

343

RESET enable_indexscan;

344

RESET enable_bitmapscan;

345

346

DROP TABLE onek_with_null;

Older »